It is 12:15am but my NOW() thinks it is yesterday

Question

I'm having a bit of a tricky issue here. My database queries are inaccurate because the NOW() function appears to have the wrong timezone.

First off, after connecting to the database, I set the appropriate timezone using the following code:

ini_set('date.timezone', 'America/Toronto');

mysql_connect('localhost', 'myusername', 'mypassword') or die(mysql_error());
mysql_select_db('mydatabase') or die(mysql_error());

mysql_query("SET SESSION time_zone = 'America/Toronto'");

As you can see, I've also set the timezone via PHP as well.

The problem is that I am running the following script, and despite the fact that it is 12:24am now, it is outputting the SUM()'s for yesterday.

Here is the code I am using:

$sql = mysql_query("select 
    website,
    sum(case when date_format(from_unixtime(`date_assigned`), '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') then 1 else 0 end) AS c_today 
    from `assignments` 
    group by website
    order by website asc
    ") or die(mysql_error());

As I mentioned, c_today is giving me the SUM()'s of yesterday right now. It is causing major issues because the counts seem to be off everywhere.

What is going on here and why isn't it working properly?

I have tried using the following code within this script:

echo '<p>Current server time is: '.date('F j Y, g:ia', time()).'<br />';
echo 'Current database time is: '.date('F j Y, g:ia', mysql_result(mysql_query("select unix_timestamp(now())"), 0)).'</p>';

This echo's:

Current server time is: September 10 2014, 12:28am
Current database time is: September 10 2014, 12:28am

Any help would be greatly appreciated.

EDIT:

I have made some progress.

I have added the following to the script:

echo mysql_result(mysql_query("select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));"), 0);

This is echoing -07:00:00, meaning it is off by 2 hours. Why would it be off by 2 hours if I have explicitly used the following code after the connection?

mysql_query("SET SESSION time_zone = 'America/Toronto'");

This should make it -05:00:00, not -07:00:00.

EDIT 2:

It appears using the SET SESSION time_zone query entirely has no effect on actually setting the sessions timezone.

possible duplicate: http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql — jogesh_pi, Sep 10 '14 at 04:32
The following post my help you ... [What timezone does MySQL's NOW() follow](http://stackoverflow.com/questions/1445702/what-timezone-does-mysqls-now-follow) — Anil, Sep 10 '14 at 04:37
And I don't agree with jogest_pi about this question being a duplicate. This post's concern isn't about "how to set the timezone", it's about why the `now()` function isn't following the timezone the mysql server and php application server is set to. — Anil, Sep 10 '14 at 04:39
looks like now() is actually giving the right time. Maybe the date_assigned values are wrong. — Garr Godfrey, Sep 10 '14 at 04:58
The `date_assigned` values use PHP's time() function upon insertion. — inkd, Sep 10 '14 at 05:06
Look this is a Cheat hack work around for your issue so use it if you are still lost (now()+(60*60*24)) gives you + 1 day. apart from that I cant help you it looks so weird your server would be doing that. — Zapps Ceo, Sep 10 '14 at 05:43
I have edited the OP. It seems that despite declaring the time zone, it is using an innacurate one. — inkd, Sep 10 '14 at 05:46

score 1 · Accepted Answer · answered Sep 10 '14 at 07:55

It appears that I did not have the location strings available in the database in order to use "America/Toronto" to set the time zone. This is why it was not working.

Therefore, I changed it to the following code and it now works:

mysql_query("SET time_zone = '-5:00'");

score 0 · Answer 2 · edited May 23 '17 at 11:43

To debug your problem, as always, form hypotheses about all the possible causes and test them. In this case, you can issue various simpler queries and queries with additional SELECT columns to see what's going on.

Possible problems (one or more of these might be occurring):

The SQL server date/time and time zone are not set right. -- You tested the date/time but not the time zone, so the time could be wrong to offset an incorrect time zone. There are many docs on setting the server's time and time zone, e.g. How do I set the time zone of MySQL?
Your data rows are incorrectly timestamped. -- Insert some new values and check them. Are you using the SQL server's clock to timestamp them, or providing the timestamp when inserting rows?
Your query isn't doing what you expect. Between from_unixtime(), date_format() and its format arg (although it looks OK to me), case when (use if(test, 1, 0) instead?), or something else (it doesn't seem to query by date, just count up the number of rows with the desired date), there are many opportunities for bugs. -- Try some simpler queries and some queries that output more info, e.g. select the formatted date_assigned for each row with the desired date.

It is 12:15am but my NOW() thinks it is yesterday

2 Answers2