On 4/9/24 11:24, Lok P wrote:
>
> On Tue, Apr 9, 2024 at 10:33 PM Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> 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@aklaver.com
