The confusion is on my part… 😊
I’m trying to understand timestamps on comments. We want to do a quick report on how many comments were posted during some user-specified time frame.
So I post a comment on our site. Not as a logged in user. On the user-end of things, the comment is time stamped for 2/28 2:03 PM, which is what my local time was when I posted. So far so good.
In the database, the comment_date field holds the unix time of 1204247009 and the edit_date holds 2008-02-28 13:02:35. I’m assuming this 1 hour difference is a daylight savings time error, or maybe a local time vs server time? (We’re with enginehosting…maybe someone knows the local time of their servers?)
But what really confuddles me is that the unix time converts to Fri, 29 Feb 2008 01:03:29 GMT. That’s an 11 hour offset. How’s that making sense? Any ideas?
My problem is I’m passing in a date like 02-28-2008, and breaking it up and using mktime to convert to unixtime and using that unixtime in my sql query to compare to comment_date, but I’m not getting accurate results.
Here’s the relevant chunk of my template, and I have PHP set to parsed on Input: (the start/end time are being passed in segment_3 & segment_4, which ends up $urlsegs[4] & [5] when using $_SERVER[“PHP_SELF”];
<?php
$thisurl = $_SERVER["PHP_SELF"];
$urlsegs = explode("/",$thisurl);
$startdate = explode("-",$urlsegs[4]);
$unixstart = mktime(0,0,0,$startdate[0],$startdate[1],$startdate[2]);
echo "
Unixstart:" . $unixstart . "
";
$enddate = explode("-",$urlsegs[5]);
$unixend = mktime(0,0,0,$enddate[0],$enddate[1],$enddate[2]);
echo "
Unixend:" . $unixend . "
";
?>
{if segment_4 != ""}
<hr>
{exp:query sql="SELECT count(*) AS totalcount
FROM exp_comments
WHERE comment_date > <?php echo $unixstart; ?> AND comment_date < <?php echo $unixend; ?>
"}
{totalcount} Comments between {segment_3} - {segment_4} )
{/exp:query}
.
.
.