I'm building an app that lets users log their activities throughout the day, for example, if I do Yoga at 7 AM on September 2. The challenge I'm facing is deciding how to store these timestamps, especially when it comes to timezones. The standard advice is to store times in UTC and convert them to the user's current timezone when retrieving them, but my goal is to see the log times as they were relative to the timezone at that moment. For instance, if I was in California when I did Yoga and later check the log from New York, I want to see that I did Yoga at 7 AM PST, not 10 AM EST. To achieve this, I'm thinking of storing both the start and end times in UTC along with the timezone offsets so I can display the correct local time. However, this feels inefficient since most of the queries will be for specific calendar days. I've also considered storing relative start and end dates for simplicity. Does this approach make sense? Am I overcomplicating things? How would you recommend handling time storage in this scenario?
5 Answers
If it’s a log, you might want to save time as a string format that includes the timezone, like ISO 8601 (e.g., 2025-09-10T12:49:17+12:00). That way, you get the best of both worlds—clear timestamps and timezone info.
Saying that "times should always be stored in UTC" isn’t always true. If you're scheduling future events, UTC might mess things up due to changing timezone rules. Given your app's goal of making logs readable in local context, storing local dates and times with offsets sounds like the right approach for simplicity and clarity. Just be cautious with users who travel across time zones on the same day; that’s a special case but still manageable!
I initially thought your method of storing both local and UTC times was smart, even if it results in some denormalized fields. This approach makes indexing easier and ensures that users see the accurate times when they look back at their logs.
I think your model should use UTC for storage consistency, but the view should always present times in the format that the user prefers. This way, you maintain simplicity in the backend while also catering to user needs.
If you need to avoid losing timezone info, it’s definitely better to keep timestamps with timezone data. ISO 8601 is a great way to do that. Most databases can handle timezone-aware timestamps, and they're efficient at managing the complexities of timezone differences.
But then, you have to deal with the overhead of timezones, and UTC can avoid the confusion that comes with daylight saving time.