Date and time can be tricky when you consider time zones, client side vs server side time and determining the best patterns for handling database records based on date and time. Considerations on how to store date and time server side usually comes down to how easy it is to retrieve a record based on some time fields, how easy it is to manipulate and how readable the format is.

When it comes to storing dates and times as a database field, we cannot rely on what client sends us, we must be agnostic of what timezone it is coming from and we must use primitive types. SDKs such as firebase will allow the input of a JavaScript Date type which will convert to Firebase’s Timestamp format.

The two most reliable ways of storing time and dates (in my opinion) is using either using Unix Epoch timestamp as a number value or ISO 8601 format as a string value.

Unix Epoch

Unix Epoch is a number value indicating the amount of milliseconds that have elapsed since January 1 1970 UTC time. Any value before 1/1/1970 would be calculated with negative number value. This format is super easy to calculate ranges between 2 dates, however it is not human readable when working with it. It needs to be converted in order to decipher to time and date that the number represents (i.e. 1640998800000).

ISO 8601

ISO 8601 format has better readability to work with and has some inherently nifty features to query dates. ISO 8601 is string representing time and dates with a standardised format:

YYYY-MM-DDThh:mm:ssTZD (e.g. 1997-07-16T19:20:30+01:00)

Although it contains many numbers, it is represented as a string in our database fields which will be advantageous for queries. ISO 8601 also supports storing the string with a timezone offset for better readability.

Date Ranges

As mentioned above Unix Epoch makes it super easy to query date ranges by specifying a lower bound and an upper bound number. For example I could query any records that have date value larger than 1640998800000 and less than 1641085200000.

We can also perform the same magic on an ISO 8601 string.

Lets use this record as an example of a record I want to retrieve:

1{
2	"id": "9bfa3de6-ff05-49cb-ab2e-af0ec400b7b7",
3	"dateOfBirth": "1997-07-16T19:20:30+01:00"
4}

In order to capture this record in the range of lets say July 1997, I would make my lower bound 1997-07 and upper bound 1997-08. This is super simple in comparison to calculating the Epoch timestamp of the lower bound and upper bound by using a converter.

All dates of a certain year and/or month and/or day

Once again, using Epoch timestamps would be very cumbersome in figuring out the millisecond ranges of the targeted year, month or day. Using ISO 8601 however makes this a very simple task. In a database such a DynamoDB, if you have your date field as a Sort Key, we can query based on the “Begins with” operator, or if you are working with a collection inside JavaScript, you can use the String().startsWith() operator.

I can get all records from 1997 by adding a condition for the field to start with 1997, or all records from July 1997, I would use 1997-07, or to drill down to the day, I could use 1997-07-07, and the process can continue all the way down to the millisecond.

Edge Case: Targeting a certain day of the year (any year)

Lets say for example I wanted to target all user records with a birthday of today to send them a birthday message. In this case, I wouldn’t care what year the date of birth is, I would only care about the month and day of the year. In this case, I wouldn’t be able to use DynamoDB’s “Begins with” operator of JavaScript’s String().startsWith() method. For this particular use case, I would need to create another field for my record that is simply just the month and day. Here is an example:

1{
2	"id": "9bfa3de6-ff05-49cb-ab2e-af0ec400b7b7",
3	"dateOfBirth": "1997-07-16T19:20:30+01:00",
4	"dateOfBirthMonthDay": "07-16" 
5}

Now, I can create an index (or Sort Key) based on the dateOfBirthMonthDay field and can query using the equality operator.

Use a Library!

I recommend always using a date/time utility library for both your front end and back end code to manipulate and display dates. My favourite JavaScript library is Day.js. With Day.js I am easily able to format dates, parse dates of any format, add and subtract dates and on handle different time zones.

I hope this post has shed some insight on some useful ways of handling date and time! ✌️