Re: Issue with date/timezone conversion function

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 <loknath(dot)73(at)gmail(dot)com> wrote:

>
> On Tue, Apr 9, 2024 at 10:33 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Lok P <loknath(dot)73(at)gmail(dot)com> 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
>>
>
>
> 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'*
>