code prettify

Tuesday 13 September 2016

MySQL DATETIME VS TIMESTAMP

A question which would come sometimes to mind when starting with MySQL is whether I should use DATETIME or TIMESTAMP data type since both appear to store same date and time component.


Similarities between datetime and timestamp:
1. Values contain both date and time parts.
2. Format of retrieval and display is "YYYY-MM-DD HH:MM:SS".
3. Can include a trailing fractional seconds part in up to microseconds (6 digits) precision.
4. With the fractional part included, the format for these values is "YYYY-MM-DD HH:MM:SS[.fraction]".
5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer automatic initialization and updating to the current date and time.

But both differ in some ways as mentioned below:


Differences between DATETIME and TIMESTAMP data types.

DATETIME:
1. Supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
2. Storage Required Before MySQL 5.6.4 was 8 bytes. *
3. Storage Required as of MySQL 5.6.4 is 5 bytes + fractional seconds storage. *
4. Preserves the textual representation of the date and time.
5. A value in the supported range is saved as it is given to MySQL so lets say if you change the timezone of MySQL, the value remains same that is it stores no timezone information and is timezone independent.

Example:

By default MySQL uses the SYSTEM timezone as its timezone which is IST in my system.

mysql> CREATE TABLE `dt` (
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

mysql> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+

mysql> INSERT INTO dt VALUES ('2016-09-12 12:12:00');

mysql> SELECT * FROM dt;
+---------------------+
| dt                  |
+---------------------+
| 2016-09-12 12:12:00 |
+---------------------+

Now lets change the session timezone to CDT which has an offset -05:00 hours with respect to UTC.

mysql> SET @@session.time_zone = '-05:00';

mysql> SELECT * FROM dt;
+---------------------+
| dt                  |
+---------------------+
| 2016-09-12 12:12:00 |
+---------------------+

The result above is same irrespective of timezone.

TIMESTAMP:
1. Supported range is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. **
2. Storage Required Before MySQL 5.6.4 was 4 bytes. *
3. Storage Required as of MySQL 5.6.4 is 4 bytes + fractional seconds storage. *
4. Preserves values relative to the timezone in use.
5. A value in the supported range is saved in UTC timestamp value when the value is supplied to MySQL, so the value contains a timezone reference. While fetching the value again, MySQL will convert that value from UTC to the timezone specific value. If the timezone of MySQL is changed it has no effect on the UTC value stored but when the value is fetched it is displayed as per the current timezone of MySQL and not in the original timezone value which was stored first time. This occurs because the same time zone was not used for conversion in both directions.

An example of this would be if timezone of MySQL is currently IST and I save a value of "2016-09-12 12:12:00" into the TIMESTAMP datatype field, so when I fetch this record value from MySQL I will get the same value "2016-09-12 12:12:00". Now if I change the timezone value to CDT and fetch this record value, I will get "2016-09-12 01:42:00" which is the CDT equivalent time of IST "2016-09-12 12:12:00".

Example:

By default MySQL uses the SYSTEM timezone as its timezone which is IST in my system.

mysql> CREATE TABLE `ts` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

mysql> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+

mysql> INSERT INTO ts VALUES ('2016-09-12 12:12:00');

mysql> SELECT * FROM ts;
+---------------------+
| ts                  |
+---------------------+
| 2016-09-12 12:12:00 |
+---------------------+

Now lets change the session timezone to CDT which has an offset -05:00 hours with respect to UTC.

mysql> SET @@session.time_zone = '-05:00';

mysql> SELECT * FROM ts;
+---------------------+
| ts                  |
+---------------------+
| 2016-09-12 01:42:00 |
+---------------------+

The result above is the CDT date time equivalent of IST date time "2016-09-12 12:12:00".

References:
- https://dev.mysql.com/doc/refman/5.7/en/datetime.html
- https://dev.mysql.com/doc/refman/5.6/en/datetime.html
- http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp

* As of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

** Why is the TIMESTAMP datatype limited to 2038 years and not beyond?
- Excerpt from https://en.wikipedia.org/wiki/Year_2038_problem:
"The Year 2038 problem is an issue for computing and data storage situations in which time values are stored or calculated as a signed 32-bit integer, and this number is interpreted as the number of seconds since 00:00:00 UTC on 1 January 1970 ("the epoch").[1] Such implementations cannot encode times after 03:14:07 UTC on 19 January 2038, a problem similar to but not entirely analogous to the "Y2K problem" (also known as the "Millennium Bug"), in which 2-digit values representing the number of years since 1900 could not encode the year 2000 or later. Most 32-bit Unix-like systems store and manipulate time in this "Unix time" format, so the year 2038 problem is sometimes referred to as the "Unix Millennium Bug" by association."


- It is also known as the Y2K28 bug.

A nice illustration from wikipedia showing the Year 2038 bug.

Caution: While designing applications please consider the range limitation of TIMESTAMP datatype [1970-2038] before using it or consider using DATETIME datatype instead.