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(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)klaver(at)aklaver(dot)com