Link to home
Start Free TrialLog in
Avatar of skij
skijFlag for Canada

asked on

MySQL: Timezone issue with unix_timestamp()

This MySQL query:
SELECT unix_timestamp('2015-10-01 00:00:00')

Open in new window

returns:
1443672000 which is at 4:00 am
however I want it to return
1443657600 which is at midnight.

The problem is likely caused by a timezone issue, where a Unix Timestamp should always be in UTC time however MySQL is doing the calculation based on the time zone of the server.
Avatar of arnold
arnold
Flag of United States of America image

Add the timezone to the string
Unix_timestamp('2015-10-01 00:00:00 -0400')

Or use the TIMezone in the query, GMT/UTC is presumed if absent.
See also the conver_tz option that includes the timezone .......
Ref the date/time command from prior

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Avatar of skij

ASKER

Thank you but I am still confused.  Your first idea of adding the timezone offset to the string by hardcoding  -0400 is not an option for me because this needs to work reliably on any server independently of the timezone offset.  I don't want to have to convert the timezone or specify the timezone for the same reason.
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skij

ASKER

Thanks.  I will use this:
SELECT UNIX_TIMESTAMP(CONVERT_TZ('2015-10-01 00:00:00', '+00:00', @@global.time_zone))

Open in new window