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.

MySQL auto update date_created and date_updated columns

It's usually a good practice to have two columns called date_created and date_updated in every table. One can always use it in application and it helps in debugging too as to when a record was created and last updated in various circumstances and contexts.

This responsibility can be given to MySQL to automatically assign current time values to these columns.

In MySQL 5.6 onwards, this can be done by setting the data type of the columns to be either date time or timestamp and creating date_created column with NOT NULL DEFAULT CURRENT_TIMESTAMP  schema and date_updated column with NOT NULL DEFAULT '0000-00-00 00:00:00' as schema with attribute ON UPDATE CURRENT_TIMESTAMP.

Below is a sample schema of a table containing date_created and date_updated columns:

CREATE TABLE `time_stamp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

But there is a catch here, since this is not applicable to all MySQL versions. Earlier to 5.6 version, MySQL allows a table to have only one TIMESTAMP column with an automatic TIMESTAMP value, that is you can either have date_created or date_updated auto updated to CURRENT_TIMESTAMP not both.

Related to this the MySQL documentation has some very good info at this page: https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

Excerpt: "By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp."

So the workaround this is to have date_created column with timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' schema  and date_updated column with timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP schema.

With above, the date_updated field is correctly updated by MySQL automatically when there is an update and to update date_created field with current timestamp value, we have to explicitly pass NULL value to date_created field which will then store the CURRENT_TIMESTAMP value in the field.

Below is a sample schema for above changes:

CREATE TABLE `time_stamp` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `date_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So for a structure as above the insert query would be as below:

INSERT INTO `test`.`time_stamp`
(`id`, `date_created`) 
VALUES
(NULL, NULL);

For update the query would be:

UPDATE 
    `test`.`time_stamp` 
SET 
    `id` = '4' 
WHERE 
    `time_stamp`.`id` = 1;

Above will just update the date_updated with the CURRENT_TIMESTAMP and the date_created value will remain same as earlier. Caution needs to be taken while updating records so as not update date_created field with NULL value.

One could also say that instead of MySQL automatically updating the date_updated field, I would like the date_created field to be updated automatically by it. Yes, it is just a reverse case and can be used, in which case while updating the record one has to pass NULL value to date_updated field. But the thing is that since there will be many updates and only a one time insert so passing a NULL while a one time insert reduces the overhead on us of passing a NULL for every subsequent update and let MySQL handle it for us. But yes this is debatable and there are cases where this reverse structure can be used.

Another workaround this is to have date_created field updated with current timestamp value using  a trigger when a record is inserted.

Hope this helps :)