Re: Issue with date/timezone conversion function

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