I spend over 8 hours last night to check all the documentations, browsing various post etc and I almost fixed my problem, yet there is stil something missing that I can't understand.
I have a table which stores various user submissions with a unix timestamp (e.g. 1351867293). I am extracting this information and counting it in order to get a total number for submissions "today", "yesterday", "this week" and so on.
The code I was using (for "today submissions") was:
WHERE DATE(from_unixtime(comment_date)) = CURRENT_DATE";
So far so good, but there was a time offset and the data wasn't displayed correctly. A user here on stackoverflow suggested to use CONVERT_TZ in order to get the offset fixed.
And here is the point where I am stuck. I am located in the CST timezone and my server seems to be located in the EST timezone. So I used this code
WHERE DATE(CONVERT_TZ(from_unixtime(comment_date), 'EST', 'CST')) = CURRENT_DATE";
...but it shows me only "0" as a result, though I have submissions for today. I tried different timezones but they all give me "0" as a result. When I replace EST and CST with numbers, for example:
WHERE DATE(CONVERT_TZ(from_unixtime(comment_date), '+00:00', '+07:00')) = CURRENT_DATE";
... I get a numeric output, but the number is not right. I tried a lot of combinations, including +08:00, -05:00, but nothing seems to work.
Basically what I am asking is what values to I have to place as those two numbers, in order to get correct results? Everything else on my website works and displays correctly in my timezone (CST), I am having only problems with this query.
The IP of my server is 50.116.13.130 if that helps.
Please note that your location is not very important unless you are using JavaScript. It is the server location that is important. So, if you are in CST, and your server is in EST, then the time of all your operations will be in EST because PHP is server side.
I think the best approach is to use UTC time. You reset your server to UTC using:
date_default_timezone_set("UTC");
Once UTC is set, all your time is now reset to 0 shift from UTC and it will be easier to handle. Personally I use the following functions to switch between PHP and MySQL dates
$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );
But again, you can use timestamps. This article may provide some answers for you
I am not sure I answered your question, but I hope those are pointers for you.
Cheers.
A Unix timestamp is always in UTC, so it doesn't matter what timezone your server is set to.
However, you must make sure that you convert the string representation of "comment_date" using the correct timezone:
// Use the timezone you are in (CST). If you're having user
// submissions from all over the world, use the timezone
// from where the submission came from.
$timezone = new DateTimeZone("CST");
$date = new DateTime("2012-01-02", $timezone);
$unixTimestamp = $date->getTimestamp();
Another important thing is to notice that "2012-01-02" will be converted into "2012-01-02 00:00:00". And "2012-01-02 00:00:00" != "2012-01-02 04:03:27".
So when you want the dates for today you should query for an interval.
$today = new DateTime(); // Get current time
$today->setTimezone(new DateTimeZone("CST"));
$today->setTime(0,0,0); // Set time to midnight of today (depends on your definition of today, could also be -24h
$sql = "[..] WHERE comment_date > ".$today->getTimestamp();
I finally resolved it by using
$timezone1 = '+12:00';
$timezone2 = '-04:00';
and
DATE(CONVERT_TZ(from_unixtime(comment_date), '$timezone1', '$timezone2')) = CURRENT_DATE";
in order to get my 8 hour time difference to work. It works, though against all logic, because my server time is in UTC (+0 hours) and the output should be in CST (+8 hours). So basically
$timezone1 = '+00:00';
$timezone2 = '+08:00';
should get me the desired result, but it didn't. I have no clue why, but I am glad my first quote of code works now.