alt Hacker NewsFuture events: store the local (at the event) date and time and timezone. You’ll keep the right context even if lawmakers decide to switch things up. You want to see your doctor at 8:30 AM on Monday September 14, 2026 whether it’s daylight saving time, or standard time or “they” decide on a fractional hour offset between the time you set the appointment and the time you attend the appointment.
Past events: UTC timestamp.
What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.
Note: nothing stops you from optimizing for queries by adding a field to store (or using a calculated index for) the integer epoch offset (e.g. unix timestamps), just make sure you know which field is authoritative.
Replies
> What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.
You can just use a TIMESTAMP with no TZ data. It's functionally the same as using the string but simpler because you avoid all the string handling headaches and gain the benefit of avoiding to avoid double booking and date/time functions to answer questions like "how many appointments do I have in April?".
UTC for past events doesn't always work either. For example, historical employee punch-in times.
UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.
[flagged]
What about virtual events between participants in different time zones? Whose do you keep stable if one has their clock moved under them?