1

I have some questions about the timestamp data type. Have I understod it correctly that if I use timestamp it will store datetimes in UTC? And when I retrive the data it will be shown in local time?

If so, are there any configurations I have to do to enable this "function"?

Martin Wiklund
  • 43
  • 4

3 Answers3

0

The timestamp is converted on the way out to a string representation of the date and time. It will use the timezone settings of the MySQL server for what shows up in your result set.

The timestamp is stored internally as seconds since EPOCH ('1970-01-01 00:00:00' UTC);

That's why a lot of folks like to set their DB's to be in UTC--that way they're always getting a UTC date time back and can translate on the code side to the local time.

Timezone for the MySQL db will use internal config settings which may fallback to the what the server's system settings are.

See this on how to configure the MySQL timezone settings: http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Ray
  • 40,256
  • 21
  • 101
  • 138
0

By default TIMESTAMP stores its data in UTC then converts it back to the time zone of the SERVER that the database resides on. There are several good responses to a similar question here: Should I use field 'datetime' or 'timestamp'?

Community
  • 1
  • 1
Rafe
  • 7,036
  • 5
  • 24
  • 27
0

It's all in the manual.

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.)

To explicitly cast a time value to a specific timezone, use convert_tz()

The current time zone is part of the session - which it inherits from the MySQL global timezone, which is inherited from the system time zone.

symcbean
  • 47,736
  • 6
  • 59
  • 94