Hacker News Reader

Comment by macintux | Hacker News Reader

I work with a development team who manages data integration and migration for massive datasets, and they have sensibly set a standard that every date/time value they store in their databases will be UTC.

But they explicitly or implicitly have also decided not to store the timezone in the strings, so every single value is technically ambiguous. Absolutely drives me crazy.

Update: since there have been questions, these are strings, not native datetime values.

View on HN

Even if you stored the timezone, the values are technically ambiguous. Even when you are using globally unique timezone identifiers like 'America/New_York' instead of 'EST' and its several meanings. A timestamp such as '2025-09-13 13:00 America/New_York' could end up referring to a different instant next week due to a correction in the timezone database. Unlikely for this sort of problem to happen and need correcting in major timezones, but it has happened for less popular and historical timezones. The way for them to be non-ambiguous, representing an unchangable instant in time, is to store the timestamp converted to a timezone that will never have retroactive changes and has no daylight savings time transitions, such as UTC. At which point, storing the timezone identifier is redundant (and violates the principle of not allowing illegal values to be represented if you follow that).

I feel strongly that a string time value with no specified timezone is dangerous.

You have to make certain it is always handled as UTC, and if you export it to another system without making the timezone explicit, or anyone who is unfamiliar with the convention views it, you’ve lost the guarantee.

When you’re shuffling around petabytes of data, adding another few megabytes to include the “Z” at the end of every timestamp is hardly a major expense.

In many systems it is reasonable not to include a zone. Usually that goes hand-in-hand with the desire to use a more compact representation, such as storing a numerical timestamp with a customary interpretation (usually UTC or TAI). If you must store a string, you may as well include a zone. Using ambiguously-zoned timestamps is an invitation for bugs. I feel your pain.

> they have sensibly set a standard that every date/time value they store in their databases will be UTC.

Not sensible at all for future date/times.

Postgres always stores date/time values in UTC (if they are timezone-aware)

These are strings. And not Postgres.

Reply on news.ycombinator.com