MySQL NOW() function returns wrong time zone

Question

I have a custom plugin in Question2Answer CMS. The plugin author uses NOW() to insert a date as "creation date" of a post.

INSERT INTO `^revrate` (`userid`, `siteid`, `revrate`, `revratetitle`,`revratenote`, `revratetime`) 
VALUES (#, $, #, $, $, NOW())

The script is okay except for the fact that it inserts a time that is 8 hours ahead of my time zone.

My current time is 2020-08-31 22:15:20, and I create a post, that post will have a date like 2020-09-01 06:15:20

It doesn't really matter in most cases, but if I use another script to convert the date in the "hours/days ago" format, this will cause erroneous calculations.

I'm not sure how the MySQL function NOW() works. Does it return GMT+0 time, UTC time or just return the server time?

Answer 1

MySQL's NOW() function returns a timestamp in local time (i.e. the time in the server's timezone). From the documentation (emphasis mine):

NOW([fsp])

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

If you need the time in UTC you'd use UTC_TIMESTAMP() instead, like this:

INSERT INTO ^revrate (userid, siteid, revrate, revratetitle, revratenote, revratetime)
VALUES (#, $, #, $, $, UTC_TIMESTAMP());

---

Edit: If you want to convert a timestamp from one timezone to another you could use the CONVERT_TZ() function for that:

INSERT INTO ^revrate (userid, siteid, revrate, revratetitle, revratenote, revratetime)
VALUES (#, $, #, $, $, CONVERT_TZ(#, @@session.time_zone, '-07:00'));

@@session.time_zone in that query is the time_zone variable of the current session.

Comments

Thank you. I'm using this modified function (NOW() - INTERVAL 28800 SECOND) to show the correct time in my timezone. At least it will work with some other scripts I'm working on.

– teddydoors 2020-08-31

---

See updated answer.

– Ansgar Wiechers 2020-08-31 ✍

---

Thanks for your guide.

– teddydoors 2020-09-01