When you are designing the schema of the database for your project and you need to store dates and times, then the question about what data type to use appears. Recently, I started a project to build a REST API and I used timestamps, a total mistake. Later, I was applying to a job and again I decided to use timestamps in the project to show my skills. During the interview, the recruiter asked me why I decided to use timestamps and I realized that I didn’t make any analysis for this decision. I just wanted to try it…what a mistake.
After this, I decided to investigate the pros and cons for the different options to store dates in a database. There are multiple articles about this topic. I want to highlight this article where it is explained with details, examples and benchmarks using MySQL. Next is a too short summary about the article:
- Using DATETIME allows to make SQL queries using functions related to dates(i.e: WEEKDAY in MySQL) in the SQL server. It allows to use dates from year 1000 to 9999. Queries over DATETIME are faster than queries over TIMESTAMP. The format used to print the DATETIME is user friendly and legible.
- Using TIMESTAMP also allows to make SQL queries using functions related to dates. It allows dates until 2038 only. TIMESTAMP is lighter and it saves 1 byte of storage compared with DATETIME. The format used to print the TIMESTAMP is user friendly and legible.
- Using UNSIGNED INT allows dates until 2106. It is not possible to use CURRENT_TIMESTAMP with this data type. SQL queries over INT are much faster than DATETIME and TIMESTAMP. To use date functions, the integer should be converted to a date(using function FROM_UNIXTIME in MySQL). In this case the SQL queries are slower than DATETIME.
One of the disadvantages with TIMESTAMP is the limit of 2038. Personally, I think that the SQL servers will change this in order to preserve compatibility with existing software using this datatype. It is not the same with INT because it is a datatype not intended to be used for dates. However there are integer types using more bytes of storage like BIGINT in MySQL.
After my experience using timestamps in a REST API, definitely I will use DATETIME in next projects unless performance and storage are critical. When you are debugging, readability matters. When you see a bunch of numbers that not make sense, then you need to expend time converting those numbers to readable dates. Also working in the frontend, sometimes I needed to understand why I was getting those dates and then again it was stressful to see unintelligible numbers in the API response.
In my opinion, using UNSIGNED INT has sense if the performance of the app is critical AND you have one or multiple queries that doesn’t require to use date functions and they are in processes that run frequently in the app.