zck
11 hours ago
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
8 hours ago
I think your problem is higher up the stack. Try running this SQL
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;
zck
8 hours 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;
michael1999
7 hours 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.