MYSQL NOW() behind by 30 seconds

Question

When I do a query for NOW() mysql returns a time that is off by roughly -30 seconds from the current time on the server. Any ideas? I tried looking through the config file and found nothing. I'm running version 5.1.37

SELECT NOW()

Answer 1

Are you sure it doesn't take 30 seconds for you to run the script and then check the time on the server?

NOW() returns a constant time that indicates the time at which the statement began to execute.

You need to base your comparison on the query initialisation not completion.

If you need the time at call use SYSDATE() instead.

If all of this is fine and you still have an issue, are you definitely just using SELECT NOW() ? If you have an addition to a field in your query somewhere it might be actually adding to the NOW() column instead of the one you think it is.

If all of this is still checking out, I think it could pay to skim the Server Timezone Support MySQL article. Maybe leap seconds are being ignored or something..

Have you restarted the MySQL server just in case the server time was adjusted since the server started?

Answer 2

The timezone contexts of the server and the context where you are getting the date almost certainly have a different view on the presence of leap seconds. I suspect that the difference will be 24s rather than 30s.

You can test this by adjusting the timezone when you get the date. On various Unixes, you can use the "right/" prefix on the timezone to adjust for leap seconds and seeing how that changes things.

To fix it, you need to make sure that the server runs with the timezone environment variable set correctly.

Update:

Missed the "windows" tag. Windows time is defined to be UTC rather than TAI and so has leap seconds included. If the difference is really 24s, then you need to check that mysql is not applying a leap second adjustments as well as Windows.