How do I make MySQL's NOW() and CURDATE() functions use UTC?

Question

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?

Not excactly what you ask for but sometimes its better to use UTC_TIMESTAMP() — key_, Aug 01 '13 at 13:42
This might help you: http://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc — Timo Huovinen, Aug 29 '14 at 10:58
how about this: http://w3resource.com/mysql/date-and-time-functions/mysql-utc_timestamp-function.php — zx1986, Feb 18 '17 at 10:20

score 40 · Accepted Answer · answered Jun 03 '09 at 21:37

40

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.

answered Jun 03 '09 at 21:37

Chad Johnson

21,215
34
109
207

So, it is true for everyone that timezone = UTC must go in [mysqld_safe], and not in [mysqld]? – Jimmy Mar 03 '13 at 14:59
4

Putting it in [mysqld] makes it unable to start. But for me, putting it in [mysqld_safe] still puts dates in as local time when using NOW(). – sigurdga Oct 10 '13 at 09:41
This setting does not change mysql to UTC for me - mysql 5.5.47-0ubuntu0.14.04.1. arulraj's answer below did work for me, however. – Julian Jun 28 '16 at 20:56
Reference [here](https://dev.mysql.com/doc/refman/8.0/en/timezone-problems.html). – Pathros Nov 21 '20 at 03:31

score 37 · Answer 2 · answered Jun 11 '19 at 05:50

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

score 30 · Answer 3 · answered Jun 03 '09 at 21:19

30

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.

answered Jun 03 '09 at 21:19

MarkR

62,604
14
116
151

i've read this before, and wondered what do you do when you're developing? Do you set your development PC's time to UTC? – Brad Parks Jun 23 '12 at 13:14
6

You develop in VMs, and set the VMs clocks to UTC. Don't ever develop on your desktop PC, it's a pain in lots of ways, especially if it's a Windows PC managed by your corporate IT department who make arbitrary configuration changes via group policy on a whim. – MarkR Jun 23 '12 at 14:25
This is definitely the way to do it. After you set your VM timezone, make sure to restart the MySQL server. – DanO Sep 17 '13 at 14:47

score 14 · Answer 4 · answered Apr 13 '11 at 15:08

14

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.

answered Apr 13 '11 at 15:08

arulraj.net

4,579
3
36
37

In my case (xampp instalation on Windows), the file my.cnf doesn't exist. I edit my.ini (path: C:\xampp\mysql\bin) and add in [mysqld] section your answer. Then I restart the server and is ok – Carlos Gómez Mar 08 '19 at 09:19
how do you restart mysql? You mean restart apache? – user2060451 Oct 24 '19 at 04:38
Will this be affected by daylight savings time? I mean I think GMT is +0:00, but still has DST so for half the year it would be off by one hour from UTC? – Stijn de Witt Oct 27 '21 at 08:22

score 11 · Answer 5 · answered Mar 03 '15 at 05:08

11

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.

answered Mar 03 '15 at 05:08

Dan

1,925
3
22
28

2

Note that this requires the [timezone tables to be populated](https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html) – Dezza Oct 06 '16 at 12:56
3

You can also write this as `CONVERT_TZ( NOW(), 'SYSTEM', 'UTC' )` to use whatever timezone the MySQL server is currently set to use. – Matthew Clark Dec 07 '18 at 15:47
2

To not be dependent on the timezone tables to be populated, use `CONVERT_TZ( NOW(), 'SYSTEM', '+00:00' )` – nl-x Jul 08 '20 at 16:27
Or you could simply call `UTC_TIMESTAMP()` instead? I think OP wants a solution that does not require code changes. – Stijn de Witt Oct 27 '21 at 08:24
@nl-x Does that take daylight savings in account? Or does `+00:00` effectively just set GMT and you still are off by an hour for half of the year? – Stijn de Witt Oct 27 '21 at 08:25
@StijndeWitt I don't know for sure. +00:00 should mean UTC which does not have DST. But it is a small test to do on your local development machine. Do let us know, if you test it. – nl-x Oct 27 '21 at 21:51
@nl-x or use the name `'UTC'`, which you know for sure is UTC. Many programming mistakes come from using something (like `'+00:00'`) and then assuming it's the same as something else. Only to be bitten by it later. Indeed you should test it first. Or read the manual. But I'm playing it save and use `'UTC'` whick I *know* is UTC and not something else. – Stijn de Witt Nov 04 '21 at 13:00
@StijndeWitt That might require your timezones table to be populated. I'm not sure though. – nl-x Nov 04 '21 at 22:12

score 5 · Answer 6 · answered Jun 03 '09 at 21:23

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()

score 0 · Answer 7 · answered Jun 03 '09 at 21:21

0

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.

answered Jun 03 '09 at 21:21

websch01ar

2,103
2
13
22

How do I make MySQL's NOW() and CURDATE() functions use UTC?

7 Answers7

Linked