MySQL DATETIME versus TIMESTAMP

MySQL DATETIME versus TIMESTAMP

MySQL DATETIME versus TIMESTAMP is a very important topic of debate for web developers. Both of them have the same quality but may not be of the same quantity though! Well, I would detail on that a bit later. First of all, let us take a glimpse of these two data types and monitor how well they fit into server side languages like PHP.

DATETIME versus TIMESTAMP – individual qualities

As I have mentioned in one of my articles, MySQL TIMESTAMP and its usage, both DATETIME and TIMESTAMP are very unique and similar at the same time. Both of these data types can store date-time values along with a fractional second count. Since they have the same scope, you might use any one of them as data type for a database attribute that stores date and time together. But, the actual flavor of DATETIME versus TIMESTAMP is in the fact that they exceed one another at certain qualities.

My article, MySQL DATETIME explained clearly portrays one of the finest qualities of MySQL DATETIME data type. DATETIME has a large lower and upper range in terms of data storage. TIMESTAMP falls short in this case. DATETIME therefore, can store dates way back in the 11th century whereas TIMESTAMP can go only up to the 1970s.

One important advantage of TIMESTAMP over DATETIME is that when the database stores a particular date and time in TIMESTAMP field, the value is changed to UTC and stored. When you retrieve the value, you get the value back in the set time zone. This feature is awesome! Isn’t it? Well, it is fabulous because if you are a developer who needs to manage loads of times and time zones together in your web application, values changed to UTC is very helpful. So, whenever you need you can retrieve values according to dynamically set time zones. But remember, even if you do not want multiple time zone values you can get one if you are careless about your code. To avoid having differences in values of timestamps, always save and retrieve database timestamp values under the same time zone.

When discussing about MYSQL DATETIME versus TIMESTAMP, we certainly must not forget one more point in the favor of TIMESTAMPS – the ON UPDATE feature. Both DATETIME and TIMESTAMP can be assigned this feature manually since the release of MySQL 5.6.4. But, TIMESTAMP have an extended feature. In a database table, the first field with a TIMESTAMP data type automatically absorbs the feature of a DEFAULT CURRENT TIMESTAMP and ON UPDATE CURRENT TIMESTAMP.

DATETIME versus TIMESTAMP – where and when

Since DATETIME has a large range of value storage, I would suggest that you always select a DATETIME data type for fields like birthday. Several people might have birthdays before 1970. So if you use TIMESTAMP data type, the value entered would be discarded and 1970-01-01 would be stored be stored for you.

TIMESTAMPS are to be used in web based applications and portals where time zones are a crucial factor. The UTC factor would help you a lot in your quick calculations and time zone shifts.

Comments are closed.