MySQL Date Types and Time Zone support

Introduction

This page presents the MySQL temporal types (DATE, TIME, DATETIME and TIMESTAMP) and explains how they are impacted when used from clients in different time zones.

This page is part of the MySQL administration series:

Date Types

These are MySQL temporal types:

  • DATE – range “1000-01-01” to “9999-12-31”
  • TIME – range “-838:59:59” to “838:59:59”.
  • DATETIME – range “1000-01-01 00:00:00” to “9999-01-01 23:59:59”.
  • TIMESTAMP – range “1970-01-01 00:00:01” UTC to “2038-01-19 03:14:07” UTC.
  • YEAR – range 1901 to 2155.

The default MySQL format for DATE values is “YYYY-MM-DD” and for TIME values is “HH:MM:SS”. Types with both date and time are formatted as “YYYY-MM-DD HH:MM:SS”. TIME is longer than 24 hours because it can also represents elapsed time.

DATETIME, TIMESTAMP and TIME can include fractional seconds up to 6 digits. A fractional second is formatted as “YYYY-MM-DD HH:MM:SS.ssssss”. To define a temporal column containing fractional seconds you provide the number of fractional digits in parenthesis:

TIMESTAMP and Time Zones

MySQL stores TIMESTAMP values in Universal Coordinated Time (UTC). When you save a TIMESTAMP value, MySQL converts it to UTC. When you retrieve the value, MySQL converts it back to the connection time zone. This conversion happens with TIMESTAMP values only. DATETIME values, on the other hand, are not converted.

The example below shows what happens to DATETIME and TIMESTAMP values when the time zone changes.

Time Zone Configuration

There are two system variables that control the server time zone:

  • system_time_zone contains the time zone of the operating system. This variable is set at start up and does not change.
  • time_zone contains the default time zone that MySQL uses for new connections. By default, it is set to SYSTEM, which takes the value from system_time_zone.

You can use the --default-time-zone option of mysqld to change the time_zone variable at start up. It is however recommended to leave the sever time zone to SYSTEM and change the client time zone instead, as what ultimately drives the time zone is the client connection.

Clients can change the connection time zone with the SET time_zone command:

The following statement shows current values for both the global and session time zone variables.

Graphical Time Zone Example

The following picture represents graphically how MySQL deals with time zones.

There are two MySQL clients connected, one using the default time zone (“+01:00”), and another using a different time zone (“+03:00”). When the INSERT client save a time stamp for “11:00:00”, MySQL server converts it to UTC and saves it as “10:00:00”. When the SELECT client retrieves the time stamp, MySQL server converts it to the client time zone as “13:00:00”.

MySQL Time Zone Example

 

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply