From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Lok P <loknath(dot)73(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Issue with date/timezone conversion function |
Date: | 2024-04-09 17:03:28 |
Message-ID: | 3756920.1712682208@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-04-09 17:09:35 | Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog |
Previous Message | Adrian Klaver | 2024-04-09 16:58:34 | Re: Issue with date/timezone conversion function |