MySQL – When to Use TIMESTAMP or DATETIME – Difference Between TIMESTAMP or DATETIME

This is a one of the most popular question, I often receive when MySQL Developers are creating a database. There are multiple datatypes which can store DateTime datatype in MySQL. The usual confusion comes up between DATETIME and TIMESTAMP.

DATETIME and TIMESTAMP – both of them can store datetime data just fine and retrieve them back, hence the question is which one to use and why?

Here are two major factor which can help you to decide which one of them, you should use for your database design.

Range

The supported range for DATETIME type is ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′.

The supported range for TIMESTAMP type is ‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC.

That means if you want to store date which is before the year 1970 or after the year 2038 you will need to use DATETIME.

Conversion

As per the MySQL official documentation – 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 means, if your application is such where you want time to stay absolutely steady with respect to GMT, you must use TIMESTAMP, or else you should use it with DATETIME.

For example, if I am using using forum, I will use TIMESTAMP as I want to capture the time when user have left comments, but if I am using an application where I have to deliver goods as per local time (and my timezone is changing), I will use DATETIME.

Summary

If you want higher range, use DATETIME and if your application is timezone independent, you should use DATETIME.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

6 thoughts on “MySQL – When to Use TIMESTAMP or DATETIME – Difference Between TIMESTAMP or DATETIME

  1. Good info, but please double check your Summary – “If you want higher range, use DATETIME and if your application is timezone independent, you should use DATETIME.” Shouldn’t the last one be “TIMESTAMP”? And can you clarify WHY you would use DATETIME when you are changing timezones – I would have thought you wanted TIMESTAMP there.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s