When it comes to storing date/time in MySQL, several data types are commonly used. My colleagues and I had a brief discussion today on which type we should use for storing created and last updated date/time.
Folks were divided into two camps. Those who preferred to work directly with UNIX timestamp wanted bigint, while people who wanted to be able to read date and time visually and not rely on unix timestamp converters wanted timestamp. To make the discussion more heated, I reminded everyone not to forget there’s also datetime.
To sum up the key points of the discussion:
- If it’s important to quickly make sense of the data as date and time, a string-style data type such as
datetimeandtimestampwould be more sensible; otherwise, storing UNIX timestamp numerals directly asbigintworks slightly faster with languages that has better support for UNIX timestamp. biginttakes 8 bytes whiledatetimetakes 5 bytes to be precise to seconds and additional 1-3 bytes for fractions depending on how many decimal places are needed (up to 6 digits after the decimal point).timestampon the other hand only takes 1 byte less thandatetimefor the same precision.- Use
timestamponly for current events, such as created_at and updated_at, but never historical or futuristic events such as date of birth or expiration of a lease. If you noticed thattimestamptakes 1 byte less thandatetimein the previous point, you should know why —timestampvalues cover only from Year 1970 to some time on 19 January 2038, a very tiny range.
And don’t forget, in MySQL, datetime has default NULL value, whereas timestamp comes with a default NOT NULL property. So if you want to explicitly assign a NULL value to a timestamp column, you must make the timestamp column nullable by adding the NULL keyword in the schema.
All the research is done using MySQL 8.0 documentation.
Links: