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:
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;
> 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.
skullone•8mo ago
crote•8mo ago
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.