CODEWITHSUNDEEP
mysql
Marik Sh
Marik Sh

Reputation: 741

MYSQL SELECT returns timestamp with timezone calculation

I have a TIMESTAMP field in my table.

When I insert a new row with a timestamp value, the value is saved correctly. but when I'm making a SELECT request (via sequelize), I get the timestamps formatted to -2 hours (the difference from my timezone and the UTC timezone)

is there a way to force the SQL to always return the UTC timezone?

Upvotes: 3

Views: 926

Answers (1)

George Pant
George Pant

Reputation: 2117

From the documentation

https://dev.mysql.com/doc/refman/5.5/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis.

So the reason you get the time difference when you retrieve is because MySQL converts it to your server's time upon retrieval.

From the documentation again: https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

The initial global server time zone value can be specified explicitly at startup with the --default-time-zone option on the command line, or you can use the following line in an option file:

default-time-zone='timezone'

If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:

SET GLOBAL time_zone = timezone;

So if you want to change your MySql's server timezone to UTC you should use:

In my.ini file (restart needed) 

default-time-zone = '+00:00'

Runtime

(If you want to set the timezone per connection just ommit GLOBAL)

SET GLOBAL time_zone = '+00:00';

Upvotes: 4

Related Questions