Part 1 highlighted the complexity of handling date and time. Part 2 focuses on design-time considerations, including schema, validation, and defining operations.
I have been part of multiple teams where we had to design data schemas. When it came to storing date and time, we just slapped on the ISO 8601 format (which, if you ask me, is the only format you would ever need). ISO 8601 format has date, time, and timezone information. Most programming languages also have good support for this format.
Let’s look at how to design a date-time data field.
Purpose
Why do you want to save a date or time in a DB?
Is it an immutable field because you are saving information about an event in the past? Or are you saving a future date or time?
What are you saving – date, time, or a timestamp? Why can’t it have a time component if it’s only a date? Why should/shouldn’t it carry timezone info?
What will the user do with the information? Will they edit it, download this information, or query it? If it is only querying, what search patterns would the user use?
All these questions help you to narrow down the exact requirement for having a date-time field.
Actions
Once these fields are exposed to systems and users, do we know what operations are expected or permitted? Even if it’s the simplest of operations like retrieve/fetch, a bit of complexity is involved.
For example, the ability to search between date and time. When the data is retrieved, should it be presented in a particular format or not? Should the response respect the timezone of the field or the timezone where the user is at?
Metadata
Once the purpose and actions are clear, we can decide whether to store metadata or additional information about the field. There would be some action that we want to support, but it can only be fulfilled with the help of the metadata.
Additional metadata information comes at the expense of space and bandwidth.
Timezone
While handling dates, time, or timestamps, considering time zones is a good practice. When you start building, saving the timezone information might not make sense. But it would be a good idea to take a step back and evaluate whether a time zone provision should be made.
Time zone information can be part of the main timestamp 2023-03-16T01:56:24+05:30 (ISO 8601) or it can be part of the metadata stored as an offset or as a name that follows the Area/Location format.
If the time zone has to be stored separately, I would prefer the area/location format for two reasons,
- It’s a standard format understood by most systems and supported by most languages.
- It’s not just an offset; it carries the location details also.
For example, the timezone offset +10:00 can be one of Australia/Brisbane, Australia/Canberra or Australia/Melbourne.
An employee table
Let’s design an Employee table. The possible date and time fields in the table would be,
- An attribute to track when the account was created. Let’s call this
createdAt - An attribute to set a date until when the account is valid. Let’s call this as
validTill
Purpose
- Track when the account was created.
- Know when the account expires.
- Update and extend the account validity.
Actions
Account creation date or createdAt by nature should be immutable. Querying must be the only operation that can be performed. A sample query then would be to get a list of employees that joined between 1st and 10th Jan 2022.
Validity or validTill will also support a similar query operation. Along with that, we would also need an update operation. Think of an API that allows an admin to extend the validity by adding additional days, months, or years.
A hypothetical API request to extend the validity by ten days could be,
PUT /account/<userId>/extend
{
"days": 10
}
Support for bulk importing users using a CSV/Excel file is another feature that can be built. This feature could import new users or update the details of existing users. A natural extension to this feature is to support exporting user details. The end user can set a filter on the data and then export the filtered content.
Metadata
We will take similar approaches for createdAt and validTill.
- We will pick a format and not save these fields as a random string.
- We will use the ISO 8601 format
- It doesn’t matter from which timezone the user was created; the
**createdAt**attribute will always store data in Zulu or UTC+00:00 timezone. - This format would be handy when accounts are created across time zones.
- A field to capture the timezone information
But by supporting the import and export of data, there could be a case where the user wants to export the data after importing it, and the exported data must have the same validTill value as it was present in the import file. If the file used to import the data had validTill set to March 25, 2025 and we parsed and saved it as 2022-03-25T12:30:42Z, we no longer have the original data.
It might be prudent, under such situations, to have an additional field that will capture the user-provided data in the file. For an employee table, this might not make sense. But think of a payments table where the user can bulk import payment information. The user-provided data would be helpful for any forensic investigations.
Designing date and time fields is a complex process that requires careful consideration of the purpose, actions, and metadata involved. Metadata can support more complex actions, but it should be stored only when necessary to avoid wasting space and bandwidth. Finally, considering time zones is an essential part of handling date and time data, and storing timezone information can be done in various ways, including as part of the main timestamp or metadata. By following these considerations, developers can design robust and effective data schemas for their applications.
./J
ChatGPT wrote the paragraph above. I didn’t know how to conclude, so I took some help. 🤷🏽♂️
- Part 1 – The Date