public inbox for [email protected]
help / color / mirror / Atom feedIssue with date/timezone conversion function
2+ messages / 2 participants
[nested] [flat]
* Issue with date/timezone conversion function
@ 2024-04-09 15:43 Lok P <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Lok P @ 2024-04-09 15:43 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Issue with date/timezone conversion function
@ 2024-04-09 15:56 Adrian Klaver <[email protected]>
parent: Lok P <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-04-09 15:56 UTC (permalink / raw)
To: Lok P <[email protected]>; pgsql-general <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-09 15:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-09 15:43 Issue with date/timezone conversion function Lok P <[email protected]>
2024-04-09 15:56 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox