On 4/9/24 11:24, Lok P wrote:
On Tue, Apr 9, 2024 at 10:33 PM Tom Lane <tgl@xxxxxxxxxxxxx
<mailto:tgl@xxxxxxxxxxxxx>> wrote:
'EST' is going to rotate to UTC-5, but that's probably not what
you want in the summer. I'd suggest AT TIME ZONE 'America/New_York'
or the like. See
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES <https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES>
regards, tom lane
Thank you so much. You are correct. The AT TIME ZONE
'America/New_York' is giving correct EST time conversion.
But I think regarding why it looks to be shifting i.e. the same time
duration appears to be holding a different count of transactions while
the base table is not getting updated/inserted/deleted for its
historical create_timestamps, I suspect the below conversion part.
The task is to count each ~15minutes duration transaction and publish in
ordered fashion i.e. something as below, but the way it's been written
seems wrong. It's an existing script. It first gets the date component
with truncated hour and then adds the time component to it to make it
~15minutes interval. Can it be written in some simple way?
9-apr-2024 14:00 12340
9-apr-2024 14:15 12312
9-apr-2024 14:30 12323
9-apr-2024 14:45 12304
/DATE_TRUNC('hour', create_timestamp AT TIME ZONE '/America/New_York'/) +/
/(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE
'/America/New_York/') / 15 * 15) * INTERVAL '15 minute'/
Something like?:
create table dt_bin_test(id integer, tz_fld timestamptz);
insert into dt_bin_test values(1, '2024-04-01 8:15'), (2, '2024-04-01
9:01'), (3, '2024-04-01 9:16'), (4, '2024-04-01 9:45'), (5, '2024-04-01
8:15'), (6, '2024-04-01 9:01');
select count(tz_fld), date_bin('15 minutes', tz_fld, '2024-01-01') as
bin from dt_bin_test group by date_bin('15 minutes', tz_fld, '2024-01-01');
count | bin
-------+------------------------
2 | 2024-04-01 09:00:00-07
2 | 2024-04-01 08:15:00-07
1 | 2024-04-01 09:15:00-07
1 | 2024-04-01 09:45:00-07
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
