public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Lok P <[email protected]>
To: Tom Lane <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Issue with date/timezone conversion function
Date: Tue, 9 Apr 2024 15:35:11 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKna9VY1vTjhidfHoY0mYs8Cct+NPPZ+2L7K3rdT45W1fAZn4w@mail.gmail.com>
References: <CAKna9VY+_b3Cf=BEopo9Z6XcEPsaXSsUUaLJ48YMSMbqT+cSug@mail.gmail.com>
	<[email protected]>
	<CAKna9VY1vTjhidfHoY0mYs8Cct+NPPZ+2L7K3rdT45W1fAZn4w@mail.gmail.com>

On 4/9/24 11:24, Lok P wrote:
> 
> On Tue, Apr 9, 2024 at 10:33 PM Tom Lane <[email protected] 
> <mailto:[email protected]>> 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
[email protected]

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Issue with date/timezone conversion function
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox