Feedback-ID: i76984098:Fastmail
Message-ID: <f90dfc6f-b3af-4a10-8487-7963e725205c@aklaver.com>
Date: Tue, 9 Apr 2024 15:35:11 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Issue with date/timezone conversion function
To: Lok P <loknath.73@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAKna9VY+_b3Cf=BEopo9Z6XcEPsaXSsUUaLJ48YMSMbqT+cSug@mail.gmail.com>
 <3756920.1712682208@sss.pgh.pa.us>
 <CAKna9VY1vTjhidfHoY0mYs8Cct+NPPZ+2L7K3rdT45W1fAZn4w@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKna9VY1vTjhidfHoY0mYs8Cct+NPPZ+2L7K3rdT45W1fAZn4w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/f90dfc6f-b3af-4a10-8487-7963e725205c%40aklaver.com>
Precedence: bulk

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