How do I make MySQL's NOW() and CURDATE() functions use UTC?
MysqlTimezoneUtcMysql Problem Overview
I want to make it so calls to NOW() and CURDATE() in MySQL queries return the date in UTC. How do I make this happen without going through and changing all queries that use these functions?
Mysql Solutions
Solution 1 - Mysql
Finally found what I was looking for...
In my.cnf,
[mysqld_safe]
timezone = UTC
I was putting this option under [mysqld], and mysql was failing to start.
Calling "SET time_zone='+0:00';" on every page load would also work, but I don't like the idea of calling that query on every single page load.
Solution 2 - Mysql
Set your server's clock to UTC. No really.
If you can do it, do do it.
One of the biggest headaches is "cron" jobs etc, running in local time zone, this means that some cron jobs will get missed once a year, and all the rest run at a different time GMT for half the year (I'm assuming you're in a time zone which has daylight saving time here).
MySQL has time zone support, but it's crazy and messed up. Don't use it if you don't have to. Just set your server's clock to UTC and time will start working how it should.
I know that changing the server clock is a major change for any managed system, and you may have a large number of servers and services which could be affected, but please, try to do it anyway. The QA work may be significant, but try.
Solution 3 - Mysql
UTC_TIMESTAMP()
Returns the current UTC date and time as a value in 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss.uuuuuu format, depending on whether the function is used in a string or numeric context.
UTC_DATE()
Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
UTC_TIME()
Returns the current UTC time as a value in 'hh:mm:ss' or hhmmss.uuuuuu format, depending on whether the function is used in a string or numeric context.
MySQL reference: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp
Solution 4 - Mysql
Goto /etc/mysql/my.cnf file and add this below line under [mysqld] section
> default-time-zone = '+00:00'
Then restart your mysql. Now select curtime(); shows the GMT time.
Solution 5 - Mysql
If changing the timezone on your running production servers or updating a key configuration setting and restarting mysql seems unrealistic and/or overkill, try this:
CONVERT_TZ(NOW(), 'US/Pacific', 'UTC')
Where US/Pacific is the timezone your NOW() call is returning the time in.
Solution 6 - Mysql
The proper way to do this is to change your server's time zone to UTC, just as MarkR said.
However, it's also possible to use SET time_zone to change the time zone for your current session.
From the manual: > The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME()
Solution 7 - Mysql
You will need to use the SET TIMESTAMP statement to format the datetime results in the desired format. This will mean changing all those queries. sysdate() will not obey this though.