Subject: Re: Issue with date/timezone conversion function

From: Tom Lane <[email-masked]>
Date: Tue, 09 Apr 2024 13:03:28 -0400

Lok P <[email-masked]> writes:
> These tables are INSERT only tables and the data in the create_timestamp
> column is populated using the now() function from the application, which
> means it will always be incremental, and the historical day transaction
> count is going to be the same. However surprisingly the counts are changing
> each day when the user fetches the result using the below query. So my
> question was , if there is any issue with the way we are fetching the data
> and it's making some date/time shift which is why the transaction count
> looks to be changing even for the past days data?

Well, your cutoff time "CURRENT_TIMESTAMP - INTERVAL '10 day'" is
constantly moving, so that'd account for shifts in what's perceived
to belong to the oldest day.  Maybe you want "CURRENT_DATE - 10"
instead?

> And also somehow this
> conversion function "DATE_TRUNC('hour', create_timestamp AT TIME ZONE
> 'EST')" is showing time in CST but not EST, why so?

'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

			regards, tom lane

---

From: Lok P <[email-masked]>
Date: Tue, 9 Apr 2024 23:54:11 +0530

> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

 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'*

---

From: yudhi s <[email-masked]>
Date: Wed, 10 Apr 2024 00:19:29 +0530

Below should work...

date_trunc('hour', timestamp_column *AT TIME ZONE '*America/New_York')
+ (((date_part('minute', timestamp_column *AT TIME ZONE
'*America/New_York')::int
/ 15)::int) * interval '15 min')

On Tue, Apr 9, 2024 at 11:54 PM Lok P <[email-masked]> wrote:

>  Thank you so much. You are correct. The AT TIME ZONE 'America/New_York'
> is giving correct EST time conversion.
>
> *(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE '*America/New_York*')
> / 15 * 15) * INTERVAL '15 minute'*
>

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 9 Apr 2024 15:35:11 -0700

On 4/9/24 11:24, Lok P wrote:
> 
> On Tue, Apr 9, 2024 at 10:33 PM Tom Lane <[email-masked] 
> <mailto:[email-masked]>> wrote:

>     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.
> 
> 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.
> 
> 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-masked]