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
[email protected]
