On 11/21/25 16:38, Adrian Klaver wrote:
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
Or something like:
select extract(epoch from current_date);
extract
------------
1763683200
select to_timestamp(extract(epoch from current_date));
to_timestamp
------------------------
2025-11-20 16:00:00-08
select extract(epoch from current_date::timestamptz);
extract
-------------------
1763712000.000000
select to_timestamp(extract(epoch from current_date::timestamptz));
to_timestamp
------------------------
2025-11-21 00:00:00-08
Where the latter does the rotation to the TimeZone setting via
::timestamptz and you get 28,800 second(8 hr) difference and a returned
timestamptz that is correct for the TimeZone.
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]
