5

My system timezone is UTC+3.

When I work with timestamp columns in MySQL, they are output for my timezone. So if a timestamp column has 00:00 UTC, it is shown as 03:00 for me.

I need to fill timestamp column with values using STR_TO_DATE.

For example I do: 

INSERT INTO `dates`(`created`) 
VALUES (STR_TO_DATE('2016-11-01 00:00:00', '%Y-%m-%d %H:%i:%s'))

Inserted value is shown as it was: 2016-11-01 00:00:00.

My conclusion is: STR_TO_DATE considers its input to be in system timezone (UTC+3).

But I have datetime strings in UTC timezone. What should I do? The query is running inside Liquibase migration, so I can't use variables like @@global.time_zone for timezone conversions.

Mikhail Batcer
  • 1,938
  • 7
  • 37
  • 57

1 Answers1

1

When you have a TIMESTAMP column and you initialize it with a string such as '2016-11-01 00:00:00', it is interpreted in the time zone associated with the current connection, which, however, may not be the same as the system time zone, and then converted to UTC for storage. To interpret the date literal in the UTC timezone, you will have to first set the time zone on the current connection with the following SQL:

SET time_zone = 'UTC';
Booboo
  • 38,656
  • 3
  • 37
  • 60