Issue with date/timezone conversion function

Started by Lok Palmost 2 years ago8 messagesgeneral

loknath.73@gmail.com

almost 2 years ago

Hi All,
It's version 15.4 of postgresql database. Every "date/time" data type
attribute gets stored in the database in UTC timezone only. One of the
support persons local timezone is "asia/kolkata" and that support person
needs to fetch the count of transactions from a table- transaction_tab and
share it with another person/customer who is in the EST timezone, so
basically the transaction has to be shown or displayed the EST timezone.

We are using below three queries for displaying each 15 minutes , hourly
and daily interval transaction counts from the table based on the
create_timestamp column (in which data is stored in UTC timezone in the
database but it has to be displayed to the user in EST timezone).

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? 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?

******For fetching 15 minutes interval transaction count

SELECT
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST') +
(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE 'EST') / 15 * 15) *
INTERVAL '15 minute' AS sample_time_interval,
COUNT(*) AS count_1
from transaction_tab
WHERE create_timestamp > CURRENT_TIMESTAMP - INTERVAL '10 day'
GROUP BY
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST') +
(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE 'EST') / 15 * 15) *
INTERVAL '15 minute'
ORDER BY sample_time_interval;

******For fetching hourly interval transaction count

SELECT
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST') AS
sample_time_interval,
COUNT(*) AS count_1
from transaction_tab
WHERE create_timestamp > CURRENT_TIMESTAMP - INTERVAL '10 day'
GROUP BY
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST')
ORDER BY sample_time_interval;

******For fetching daily interval transaction count

SELECT
DATE_TRUNC('day', create_timestamp AT TIME ZONE 'EST') AS
sample_time_interval,
COUNT(*) AS count_1
from transaction_tab
WHERE create_timestamp > CURRENT_TIMESTAMP - INTERVAL '10 day'
GROUP BY
DATE_TRUNC('day', create_timestamp AT TIME ZONE 'EST')
ORDER BY sample_time_interval;

Adrian Klaver

adrian.klaver@aklaver.com

almost 2 years ago

In reply to: Lok P (#1)

Re: Issue with date/timezone conversion function

On 4/9/24 08:43, Lok P wrote:

Hi All,
It's version 15.4 of postgresql database. Every "date/time" data type
attribute gets stored in the database in UTC timezone only. One of the
support persons local timezone is "asia/kolkata" and that support
person needs to fetch the count of transactions from a table-
transaction_tab and share it with another person/customer who is in the
EST timezone, so basically the transaction has to be shown or displayed
the EST timezone.

What is the datatype for the create_timestamp?

What does SHOW timezone; return on the server?

--
Adrian Klaver
adrian.klaver@aklaver.com

Lok P

loknath.73@gmail.com

almost 2 years ago

In reply to: Adrian Klaver (#2)

Re: Issue with date/timezone conversion function

On Tue, Apr 9, 2024 at 9:26 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 4/9/24 08:43, Lok P wrote:

Hi All,
It's version 15.4 of postgresql database. Every "date/time" data type
attribute gets stored in the database in UTC timezone only. One of the
support persons local timezone is "asia/kolkata" and that support
person needs to fetch the count of transactions from a table-
transaction_tab and share it with another person/customer who is in the
EST timezone, so basically the transaction has to be shown or displayed
the EST timezone.

What is the datatype for the create_timestamp?

What does SHOW timezone; return on the server?

Thank you for the quick response.

The column data type for "create_timestamp" is "timestamptz'.
Show timezone from the support users client machine UI showing
"Asia/Calcutta".
Not having access to run "Show timezone" on the server currently, I will
try to get it.
output from pg_setting showing setting as "Asia/Calcutta', reset_val as
"Asia/Calcutta", boot_val as "GMT"

Adrian Klaver

adrian.klaver@aklaver.com

almost 2 years ago

In reply to: Lok P (#3)

Re: Issue with date/timezone conversion function

On 4/9/24 9:16 AM, Lok P wrote:

On Tue, Apr 9, 2024 at 9:26 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 4/9/24 08:43, Lok P wrote:

Hi All,
It's version 15.4 of postgresql database. Every "date/time" data

type

attribute gets stored in the database in UTC timezone only. One

of the

support persons local timezone is "asia/kolkata" and that support
person needs to fetch the count of transactions from a table-
transaction_tab and share it with another person/customer who is

in the

EST timezone, so basically the transaction has to be shown or

displayed

the EST timezone.

What is the datatype for the create_timestamp?

What does SHOW timezone; return on the server?

Thank you for the quick response.

The column data type for "create_timestamp" is "timestamptz'.
Show timezone from the support users client machine UI showing
"Asia/Calcutta".
Not having access to run "Show timezone" on the server currently, I will
try to get it.
output from pg_setting showing setting as "Asia/Calcutta', reset_val as
"Asia/Calcutta", boot_val as "GMT"

In the pg_settings query what are the source, sourcefile, sourceline
fields set to?

--
Adrian Klaver
adrian.klaver@aklaver.com

Tom Lane

tgl@sss.pgh.pa.us

almost 2 years ago

In reply to: Lok P (#1)

Re: Issue with date/timezone conversion function

Lok P <loknath.73@gmail.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

Lok P

loknath.73@gmail.com

almost 2 years ago

In reply to: Tom Lane (#5)

Re: Issue with date/timezone conversion function

On Tue, Apr 9, 2024 at 10:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Lok P <loknath.73@gmail.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'*

yudhi s

learnerdatabase99@gmail.com

almost 2 years ago

In reply to: Lok P (#6)

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.73@gmail.com> wrote:

Show quoted text

On Tue, Apr 9, 2024 at 10:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Lok P <loknath.73@gmail.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'*

Adrian Klaver

adrian.klaver@aklaver.com

almost 2 years ago

In reply to: Lok P (#6)

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@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