Like any programmer, I used to use Date for storing datetime values in the database until I suffered from bugs related to time zones and DST (Daylight Saving Time). The most common symptom is that the date fields, such as created_date or updated_date, often display one day prior to what is set: Let’s say an admin sets the created_date to Jan 1st, 2025, 00:00:00, but clients somehow see the value Dec 31, 2024, 23:00:00.
I tried putting browser timezone information into adjusting timestamps, but then I noticed that the code base became more complex, and the bug still can’t be fixed when DST (Daylight Saving Time) occurs. The bug will happen when:
- Most databases use integers representing microseconds (or timestamps) to store
datetimedata. - Users are located in many countries with different time zones.
- Daylight Saving Time occurs on a variable schedule.
Then I realize the power of the standard: ISO 8601, is that it can be used to store date-time values in plain text and still be sortable.
Storing date-time in the format YYYY-MM-DD HH:mm:ss at UTC (or any format instructed in ISO 8601) can remove all headaches when using timestamps while also retaining sorting and filtering capabilities. The only downside is that the frontend and backend must ensure date-time data is sent and received in ISO 8601 format instead of integers, and this conversion is simple enough to help avoid days of debugging.