Datetime or Timestamp: readability matters
Tags: development, database, debugging, webdev
Published: 2020-06-12 20:43:38

---
title: Datetime or Timestamp: readability matters
published: true
date: 2020-06-12 20:39:48 UTC
tags: Development,databases,Debugging,WebDevelopment
canonical_url: https://carlosguzman.dev/datetime-or-timestamp-readability-matters/
---

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](https://www.vertabelo.com/blog/what-datatype-should-you-use-to-represent-time-in-mysql-we-compare-datetime-timestamp-and-int/). 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.

![datetime or timestamp: readability matters](https://carlosguzman.dev/wp-content/uploads/2020/06/timestamps-in-api-response.png)<figcaption>Timestamps in a REST API response</figcaption>

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.

The post [Datetime or Timestamp: readability matters](https://carlosguzman.dev/datetime-or-timestamp-readability-matters/) appeared first on [Carlos Guzman](https://carlosguzman.dev).