2

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.

fedorqui
  • 275,237
  • 103
  • 548
  • 598
Georgi Grancharov
  • 183
  • 1
  • 2
  • 8

3 Answers3

0

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.

zx81
  • 41,100
  • 9
  • 89
  • 105
Greeso
  • 7,544
  • 9
  • 51
  • 77
0

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();
luttkens
  • 1,272
  • 8
  • 16
  • My problem is, that i have this line ** WHERE DATE(CONVERT_TZ(from_unixtime(comment_date), '$timezone1', '$timezone2')) = CURRENT_DATE"; ** in over 60 files. _$timezone1_ and _$timezone2_ are parsed via the header.php - so I got to use numeric values. I tried your approach earlier, but it messed up the other datetime display of my website - the backend I am using converts the timezones normally, the only problem I have is with this custom code for the statistics - It's the only thing that messed up timezones – Georgi Grancharov Nov 03 '12 at 08:47
  • Ok I see. But if you do a select statement on DATE(CONVERT_TZ(from_unixtime(comment_date), 'EST', 'CST')), does it give what you expect? – luttkens Nov 03 '12 at 09:12
  • nope, for some reasons I can't use "EST" or "CST", the only thing working are numeric values such as "+05:00" or "-06:00". – Georgi Grancharov Nov 03 '12 at 09:16
  • Ok, but what is the type of your **comment_date** field? Unsigned integer or Timestamp? Is it stored in UTC? If it is a timestamp in UTC, you could try WHERE DATE(comment_date) = CURRENT_DATE. Perhaps I do not understand your question fully, but as a rule of thumb fpr the future - always store date in UTC. – luttkens Nov 03 '12 at 13:16
  • int(10) and it stores the timestamp in UTC. If I use "WHERE DATE(comment_date) = CURRENT_DATE" i get the results for UTC time, yet not for CST time - by using convert_cz i am trying to "add" time to the result, so everything is synchronized at the output. – Georgi Grancharov Nov 03 '12 at 14:03
  • Lets say i am a user, and i make a submission at 22:00 CST. The "todays submissions" value would be 1. But just 2 hours after, at 00:01 CST it should be "0" - yet it isn't, because WHERE DATE(comment_date) = CURRENT_DATE thinks it's still yesterday ;) – Georgi Grancharov Nov 03 '12 at 14:05
  • Ok, but if comment_date is in UTC then my second code block should work... Have you tried? You mentioned it messed up your datetime display on the website. But what if you ignore the first code block and just use the second one for the query (which has nothing to do with datetime display)? I hope it works! – luttkens Nov 03 '12 at 17:49
0

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.

Georgi Grancharov
  • 183
  • 1
  • 2
  • 8