How can I use the MySQL NOW () and CURDATE () functions using UTC?

Question

I want to make calls NOW () and CURDATE () in MySQL queries return a date in UTC. How to do this without going through and changing all the queries that use these functions?

Answer 1

Finally found what I was looking for ...

In my.cnf,

[mysqld_safe] timezone = UTC 

I put this option in [mysqld] and mysql did not start.

Call "SET time_zone = '+ 0:00';" will work on every page load, but I don’t like the idea of ​​calling this request on every page load.

Answer 2

Set the server clock to UTC. Not really.

If you can do it, do it.

One of the biggest headaches is cron work, etc., working in the local time zone, which means that some cron jobs will be skipped once a year, and all others will work at other GMT time for six months ( I Assuming you are in a time zone where daylight saving time is available).

MySQL has time zone support, but it's crazy and confused. Do not use it if you do not need it. Just set the server clock to UTC, and the time will start working as 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 that may be affected, but please try to do it anyway. QA performance can be significant, but try.

Answer 3

Open the file /etc/mysql/my.cnf and add this line below in the [mysqld] section

default-time-zone = '+00: 00'

Then restart mysql. Now select curtime (); shows GMT.

Answer 4

If changing the time zone on your production servers or updating the key configuration settings and restarting mysql seems unrealistic and / or excessive, try the following:

 CONVERT_TZ(NOW(), 'US/Pacific', 'UTC') 

Where US/Pacific is the time zone, your call to NOW() returns the time.

Answer 5

The right way to do this is to change your server’s time zone to UTC, as MarkR said.

However, you can also use SET time_zone to change the time zone for the current session.

From the manual:

The current time zone setting affects the display and storage of time sensitive zones. This includes values ​​displayed by functions such as NOW () or CURTIME ()

Answer 6

You will need to use the SET TIMESTAMP statement to format the datetime results in the desired format. This will mean a change in all of these queries. sysdate () will not honor this though.

Answer 7

 UTC_TIMESTAMP() 

Returns the current UTC date and time as a value in the format "YYYY-MM-DD hh: mm: ss" or YYYYMMDDhhmmss.uuuuuu, depending on whether the function is used in a string or numeric context.

 UTC_DATE() 

Returns the current UTC date as a value in the format "YYYY-MM-DD" or YYYYMMDD, depending on whether the function is used in a string or numerical context.

 UTC_TIME() 

Returns the current UTC time as a value in the format "hh: mm: ss" or hhmmss.uuuuuu, depending on whether the function is used in a string or numerical context.

MySQL Link: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp