This famous data structure mistake makes developers’ lives hell.
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?
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
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
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?
That doesn’t look very nice, does it? The design team gives us the following design mock-up as a requirement instead.
We will now have to transform the
text date, into a
6 January , not
1 June . That’s because moment thought we were giving it a
month/day/year date format, instead of a
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?
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.
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!