MySQL DATETIME vs TIMESTAMP

A well-designed application has many traits. Good schema design is one of them. Choosing the right data type serves as a contributor towards a good schema design. The selection of a data type depends on various questions like what is the range of values that the column will hold?, the precision required by the column?, and if any special behavior is required. Choosing an optimal data type may save space on disk and memory, and may save CPU cycles. Generally such consideration are trivial when we talk about smaller applications but are very important for large scale applications. MySQL offers two alternatives for storing date time values namely DATETIME and TIMESTAMP. Which one is the best is not a good question but which one to use and what are the characteristics of each one is a better question and knowing the answer to the latter question will help us selecting the best option.

Storage Format

When using DATETIME, MySQL stores date time information as an integer in the format of YYYYMMDDHHMMSS. When displaying this data MySQL formats this storage representation into an ANSI standard representation which is YYYY-MM-DD HH:MM:DD. On the other hand, MySQL just stores the number of seconds elapsed since January 1, 1970 12:00 AM when it comes to storing date time information as TIMESTAMP.

Range

DATETIME can hold date time information from the year 1001 to the year 9999 whereas TIMESTAMP can hold date time information from the year 1970 to the year 2038.

Storage

DATETIME uses 8 bytes of storage whereas TIMESTAMP uses 4 bytes of storage, almost half of what DATETIME uses. So if TIMESTAMP is feasible for usage, you shouldn't mind using it.

Timezone Dependence

Storage and display of date time information when using DATETIME is independent of the timezone whereas in the case of TIMESTAMP, the date time information displayed is dependent on the time zone.

Special Characteristics of TIMESTAMP

TIMESTAMP type has some characteristics that are absent in DATETIME type. Below are the characteristics:

• The display of date time when using TIMESTAMP type is dependent on the time zone. This means that MySQL will return the date time information after converting it into an appropriate time zone. By default the time zone is the server's time and can be changed on a per connection basis.

• By default, if you insert a row in the table without specifying the value of the timestamp column, MySQL will set the time of that column to be the current time. Similar is the case when a row is updated, MySQL will update the TIMESTAMP column with the current time if the date is not specified in the update query. Obviously we can change the default behaviour of the TIMESTAMP data type.

Hopefully this information will help us distinguish the two data types and to choose the right data type for the right purpose.