Re: Unexpected date conversion results

On 11/21/25 16:09, Steve Crawford wrote:
> Either there is a bug in my understanding or one in PostgreSQL. I expect
> a date value to follow the current time zone setting and be interpreted
> as midnight at the start of the given date. In many cases it does. Shown
> below are the postgresql.conf settings and the psql client settings
> showing the time zone to be America/Los_Angeles:
>
> postgresql.conf:
> log_timezone = 'America/Los_Angeles'
> timezone = 'America/Los_Angeles'
>
> Client time zone setting:
>
> steve=> show timezone;
>       TimeZone
> ---------------------
>  America/Los_Angeles
>
>

> However, extracting the epoch from current_date returns 4pm the prior
> day (i.e. 2025-11-21 00:00:00-00), in other words midnight 2025-11-21
> UTC which seems to be inconsistent behavior:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

"epoch

For timestamp with time zone values, the number of seconds since
1970-01-01 00:00:00 UTC (negative for timestamps before that); for date
and timestamp values, the nominal number of seconds since 1970-01-01
00:00:00, without regard to timezone or daylight-savings rules; for
interval values, the total number of seconds in the interval
"

So epoch is in UTC which is confirmed by below.
>
> steve=> select to_timestamp(extract(epoch from current_date));
>       to_timestamp
> ------------------------
>  2025-11-20 16:00:00-08

If you want it to work(I am in 'America/Los_Angeles' also):

select to_timestamp(extract(epoch from current_date)) at time zone 'UTC';

timezone
---------------------
2025-11-21 00:00:00

> There was a time, like version 9-dot-something, when the above queries
> performed as expected returning midnight in the current time zone but I
> haven't been able to find a change document indicating this as an
> expected change.

I don't remember that, but as the gray content of the hair increases the
memory is less solid:)

>
> -Steve

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com