Guidelines

This site is for tech Q&A. Please keep your posts focused on the subject at hand.

Ask one question at a time. Don't conflate multiple problems into a single question.

Make sure to include all relevant information in your posts. Try to avoid linking to external sites.

Links to documentation are fine, but in addition you should also quote the relevant parts in your posts.

1 vote
29 views
29 views

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?

in Scripting by (15) 2
edited by

Your answer

Preview

Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
By submitting this post you agree to our Terms & Conditions.
To avoid this verification in future, please log in or register.

1 Answer

0 votes

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.

by (30) 5
edited by
 
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.
 
See updated answer.
 
Thanks for your guide.
...