MySQL Forums
Forum List  »  Newbie

Re: Convert UTC in milliseconds to text format results in local time
Posted by: Peter Brawley
Date: August 03, 2022 04:31PM

MySQL timezone conversions can drive us mad. This behaviour ...

               @@system_time_zone: Central Standard Time
                      @@time_zone: SYSTEM
                            now(): 2022-08-03 16:23:36
                 unix_timestamp(): 1659565416
          unix_timestamp( now() ): 1659565416
                  utc_timestamp(): 2022-08-03 22:23:36
unix_timestamp( utc_timestamp() ): 1659587016

appears not to agree with ...

"If UNIX_TIMESTAMP() is called with no date argument, it returns a Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC.

"If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. The server interprets date as a value in the session time zone and converts it to an internal Unix timestamp value in UTC." (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp)

... because, as I understand it, MySQL timestamps convert to UTC based on the time_zone setting,
and convert back on retrieval.

Options: ReplyQuote


Subject
Written By
Posted
Re: Convert UTC in milliseconds to text format results in local time
August 03, 2022 04:31PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.