[michael1999]

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]

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]

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.