One place I've been bitten lately is storing timestamps in Postgres. Postgres lets you store time with a time zone -- but what that means is that upon receiving a timestamptz converts it to UTC and stores that instead.
Which is fine, in a way -- it won't store the wrong instant in time. But it also won't let you know the time the user sees. For example, you might want to tell someone when the store opens.
Fine, you say. You can look up the location of the store and use that to get the timezone. But what about a different case? What about if you want a user to test their blood sugar every day. Did they test their blood sugar on Tuesday? Well, then it depends what timezone they're in. What is the problem with having each user set their time zone? Isn't this just like the store issue?
No! Notably, unlike stores located in fixed buildings, people move around. They go on vacation. And if you don't know where they were when an event happened, you don't know what time the user was.
So it seems you have to, when you get a timestamp with time zone from a user, store the timestamptz, but also store the time zone in the database.
How frustrating, for a database that has a data type called "timestamp with time zone".
michael1999 · 3h ago
I think your problem is higher up the stack. Try running this SQL
insert into tz_test (comment, ts, ts_tz) values
( 'in "local"', TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York', TIMESTAMP WITH TIME zone '2003-04-12 04:05:06 America/New_York'),
( 'flattened to UTC', (TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York') at time zone 'UTC', TIMESTAMP WITH TIME zone '2003-04-12 04:05:06 America/New_York'),
( 'in "local" no types', TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York', '2003-04-12 04:05:06 America/New_York'),
( 'flattened to UTC no types', (TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York') at time zone 'UTC', '2003-04-12 04:05:06 America/New_York');
select comment,
ts,
ts AT TIME ZONE 'UTC' as ts_utc,
ts_tz,
ts_tz AT TIME ZONE 'UTC' as ts_tz_utc,
case when ts < ts_tz then 'less' when ts>ts_tz then 'greater' else 'equal' end
from tz_test;
zck · 2h ago
I'm not sure this exactly is what I'm thinking about. Yes, `at time zone "UTC"` does the proper conversion, so all times will represent the exact instant they should. But in no cases do you know what time zone the data came in as -- that information is thrown away.
When you look at your data, what is the time a user's watch said when the data was input? What time zone was the data input as?
Here's some queries:
create table tz_test ( comment varchar, ts_tz timestamptz );
insert into tz_test (comment, ts_tz) values
('midnight US Eastern', timestamp with time zone '2025-05-13 00:00:00-4'),
('4am UTC', timestamp with time zone '2025-05-13 04:00:00+0');
select comment,
ts_tz
from tz_test;
I would expect that one row comes out as midnight, and the other row comes out as 4am. But they both come out as midnight. That's what I don't like.
michael1999 · 2h ago
Oh. I was completely wrong. So you need another column to store the source TZ? That's terrible! I was assuming it worked like Oracle.
lcnPylGDnU4H9OF · 5h ago
Learning the story of During via this 7 year-old blog post is poetic:
> Sometimes we’d be working on something that tangentially related to time, and as kind of a recurring in-joke he’d always tell me: Zach, whatever you do: just don't ever build a calendar.
> Anyway, I’m Zach Holman and I’m [building a calendar](https://during.com).
> During is no longer a thing.
Good on them for trying.
notepad0x90 · 4h ago
I just wanna say bravo and great job on the web design. A web page that actually fills up my entire screen with content. Even though the margins are mostly unused, pictures and tips still show up there once in a while. Even on HN, I see something like a 20% margin just wasting space.
skullone · 6h ago
Lots of words to say "use already available time libraries and use ISO time formatting". Cool story bruh. And really really terrible way to communicate one of the most beaten to death categories with that site design
crote · 5h ago
Most time libraries - especially the stdlib ones - are broken in various subtle ways. ISO time formatting is neat, but only works for past events.
Your approach is usually going to be sufficient-ish for timestamping past events - which is most applications. But try to build a calendar, and you'll quickly notice that it simply doesn't work that way.
Which is fine, in a way -- it won't store the wrong instant in time. But it also won't let you know the time the user sees. For example, you might want to tell someone when the store opens.
Fine, you say. You can look up the location of the store and use that to get the timezone. But what about a different case? What about if you want a user to test their blood sugar every day. Did they test their blood sugar on Tuesday? Well, then it depends what timezone they're in. What is the problem with having each user set their time zone? Isn't this just like the store issue?
No! Notably, unlike stores located in fixed buildings, people move around. They go on vacation. And if you don't know where they were when an event happened, you don't know what time the user was.
So it seems you have to, when you get a timestamp with time zone from a user, store the timestamptz, but also store the time zone in the database.
How frustrating, for a database that has a data type called "timestamp with time zone".
create table tz_test ( comment varchar, ts timestamp, ts_tz timestamptz );
insert into tz_test (comment, ts, ts_tz) values ( 'in "local"', TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York', TIMESTAMP WITH TIME zone '2003-04-12 04:05:06 America/New_York'), ( 'flattened to UTC', (TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York') at time zone 'UTC', TIMESTAMP WITH TIME zone '2003-04-12 04:05:06 America/New_York'), ( 'in "local" no types', TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York', '2003-04-12 04:05:06 America/New_York'), ( 'flattened to UTC no types', (TIMESTAMP WITH TIME ZONE '2003-04-12 04:05:06 America/New_York') at time zone 'UTC', '2003-04-12 04:05:06 America/New_York');
select comment, ts, ts AT TIME ZONE 'UTC' as ts_utc, ts_tz, ts_tz AT TIME ZONE 'UTC' as ts_tz_utc, case when ts < ts_tz then 'less' when ts>ts_tz then 'greater' else 'equal' end from tz_test;
When you look at your data, what is the time a user's watch said when the data was input? What time zone was the data input as?
Here's some queries:
I would expect that one row comes out as midnight, and the other row comes out as 4am. But they both come out as midnight. That's what I don't like.> Sometimes we’d be working on something that tangentially related to time, and as kind of a recurring in-joke he’d always tell me: Zach, whatever you do: just don't ever build a calendar.
> Anyway, I’m Zach Holman and I’m [building a calendar](https://during.com).
> During is no longer a thing.
Good on them for trying.
Your approach is usually going to be sufficient-ish for timestamping past events - which is most applications. But try to build a calendar, and you'll quickly notice that it simply doesn't work that way.