4 ms·
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 int
by michael1999 1y 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 1y agoI'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 1y agoOh. I was completely wrong. So you need another column to store the source TZ? That's terrible! I was assuming it worked like Oracle.