Re: Issue with date/timezone conversion function

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