Developers: Stop This Date Mistake!

This famous data structure mistake makes developers’ lives hell.

Photo by krakenimages on Unsplash

Let’s have a look at a famous database structuring mistake and how we can avoid it. These mistakes can make the maintenance and further development of an application extremely difficult for no reason.


Date and time

This is the top one for developer frustrations. Wrongly structured time-related data can make it difficult to sort data, convert data, and show the correct time depending on the user’s timezone. Let’s stop this once and for all.

Date and time: The problem

Let’s take a look at the following database table users Do you see any issues with the following data?

An example `users` database table

As you might guess, something is wrong with the date and time. Let’s dig a little deeper and have a look at the table structure defined for users .

Table structure for the `users` table

We have a column id defined as primary key, with auto increment. All good so far. A username, defined as a varchar(250) , while the size of this varchar is probably too big for a username, a varchar is still a reasonable choice. Then we discover the registration_date column stored as text.

This should immediately raise alarm bells for a variety of reasons. For storing small strings we should be using the varchar data structure which is optimized for smaller pieces of text. We will see this in more detail in the next topic.

But that’s not all, imagine you are a developer, and you want to display the date on a web page. Fine, we can simply display the raw data from the database, right?

An example social media website using raw text dates

That doesn’t look very nice, does it? The design team gives us the following design mock-up as a requirement instead.

Design mock-up given by fantasy UX team

Basically, they don’t want us to display the date, but instead, the amount of time that has passed since the message. This means we need to understand exactly what the date value means in our front-end code. Let’s continue with JavaScript for some examples.

We will now have to transform the text date, into a Date object date, which JavaScript understands. From this date, we can then calculate the difference from now until that specified time. Let’s use a famous date library to make our lives easier, called MomentJS.

The moment library is useful for converting text dates into real javascript object dates. However, as you might have noticed, our 06/01/2021 was supposed to be 6 January , not 1 June . That’s because moment thought we were giving it a month/day/year date format, instead of a day/month/year format.

No problem! We can solve this. Let’s use moment’s magic to parse the string in the correct format.

We’ve got it! It was indeed 2 months ago since this is an old message. However, the timezone now seems to be wrong. Moment does not seem to have a format for fetching this timezone. Moment does have a method .utcOffset() which takes care of it, but its usage requires even more manual work, which is what we want to avoid in the first place!

All in all, we see it takes considerable effort for displaying a date correctly, due to the wrong data structure used for saving the date and time.

Date and time: The solution

Let’s find a solution to this! It can be as easy as defining a good data structure. Instead of using string dates, why not use Unix timestamps? This is the time in seconds since January 1, 1970, at midnight UTC.

With a simple command, we can get the current timestamp. What about converting it back into a date?

There’s our JavaScript date object! We didn’t even need any third-party libraries and it is really that easy.

Date and time: Datetime vs. timestamp

What about the datetime datatype offered by some database engines? They also work fine. However, timestamps work in a universal way and are usually even lighter on the databases. Integers are a really efficient data type in the world of databases.

One important difference to note between a timestamp and a DateTime is that the datetime will also store the timezone of the date. A timestamp, on the other hand, will only store the number of seconds since epoch, in UTC. You will not be able to figure out what the timestamp of the saved date was anymore. Depending on your application, you might choose to go for DateTime or timestamp. These are both very solid options. An alternative could be to store the timezone separately if that would fit your requirements better.

Date and time: Summary

While the text format can be tricky to use by developers, there are also other reasons not to opt for text dates:

  • Text dates require more storage in your database compared to integer timestamps
  • Ordering data by text dates can be very tricky and heavy. On the other hand, ordering by integer timestamps is incredibly easy for databases

Datetime and timestamps are both valid options, while timestamps are lighter, DateTime offers more features such as storing the timezone of a date.

What if it’s too late?

What if your application is already using the wrong data types? A common migration strategy would be to duplicate the data column. If you had a date column before, simply add a new column such as timestamp. Create a script that will migrate the previous data to this new column in the new format. It can be cumbersome to create a converter for the previous format, but at least you can get rid of the old data format in the future.

Step by step migrate your applications towards the new column. Once you are certain the previous data column is not used anymore in any application, it can be safely removed. When third-parties are integrating into your APIs, make sure to inform them as well, or keep it backward compatible.


Key take-aways

Think twice when working with new or old data. Think about how the data structures imposed will impact the entire architecture, going from the capabilities of filtering data, searching data, to the displaying of the data. This is not only about dates, but also holds for other items.

A quick decision at the beginning of a project can have a big impact on its maintainability in the long term. If a value is specified in an unwanted format, it causes extra stress to the database, the development team, and the project as a whole.

Brush up your database normalization techniques to avoid getting into these situations! Instead of storing multiple data in one field, always extract the field to separate columns as much as possible, until these can be independent. Your fellow developers will thank you. This is exactly what the first normal form or 1NF of the database normalization techniques instruct. Make sure the data is atomic.

As for dates, you can either use timestamps or actual date objects. Both will work fine. Depending on your architecture and project, you might have to use one or the other. If your application requires to know the timezone of the date, you will need to use a date object, as a timestamp does not hold this value.

While a proper data structure setup is important, don’t forget other aspects of databases such as indexing and other performance optimizations.

Keep your data clean, and keep your fellow developers happy!


Extra Resources

1NF, 2NF, and 3NF: the database normalization techniques.

Datetime vs. timestamp: when should you use which?

MariaDB Timestamp Documentation

Kevin Van Ryckegem

Kevin Van Ryckegem