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.klaver@aklaver.com

On Fri, 2025-11-21 at 16:38 -0800, Adrian Klaver wrote:

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:)

I dug into the git history, and it has been like that since commit b3506006b564
in 2002 (way before version 9.x). That commit fixed a bug that returned ten
time the correct reault (but still offset from the UTC epoch).

Yours,
Laurenz Albe

Steve Crawford <scrawford@pinpointresearch.com> writes:

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:

steve=> select to_timestamp(extract(epoch from current_date));
to_timestamp
------------------------
2025-11-20 16:00:00-08

The reason this is misbehaving is that there are two versions of
extract(), one for timestamp-with-timezone input and one for
timestamp-without-timezone input. The latter applies no
timezone correction, so it won't give true Unix-epoch results
unless you are in UTC zone to start with.

By default, a date will be promoted to timestamp-without-timezone not
timestamp-with-timezone, so the above doesn't give what you want.
It'd work better with a cast to force the right interpretation:

regression=# set timezone = 'America/Los_Angeles';
SET
regression=# select to_timestamp(extract(epoch from current_date));
to_timestamp
------------------------
2025-11-20 16:00:00-08
(1 row)

regression=# select to_timestamp(extract(epoch from current_date::timestamptz));
to_timestamp
------------------------
2025-11-21 00:00:00-08
(1 row)

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.

A bit of experimenting says the current behavior dates to 9.2.
I've not checked the release notes to see if it was documented,
but in any case it's stood for long enough now that I doubt
we'd change it.

regards, tom lane

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
adrian.klaver@aklaver.com

Laurenz Albe <laurenz.albe@cybertec.at> writes:

I dug into the git history, and it has been like that since commit b3506006b564
in 2002 (way before version 9.x). That commit fixed a bug that returned ten
time the correct reault (but still offset from the UTC epoch).

I didn't bisect, but I get this in 9.1.24:

regression=# set timezone = 'America/Los_Angeles';
SET
regression=# select to_timestamp(extract(epoch from current_date));
to_timestamp
------------------------
2025-11-21 00:00:00-08
(1 row)

and this in 9.2.24:

regression=# set timezone = 'America/Los_Angeles';
SET
regression=# select to_timestamp(extract(epoch from current_date));
to_timestamp
------------------------
2025-11-20 16:00:00-08
(1 row)

regards, tom lane

On Fri, Nov 21, 2025 at 4:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

I dug into the git history, and it has been like that since commit

b3506006b564

in 2002 (way before version 9.x). That commit fixed a bug that returned

ten

time the correct reault (but still offset from the UTC epoch).

I didn't bisect, but I get this in 9.1.24:

regression=# set timezone = 'America/Los_Angeles';
SET
regression=# select to_timestamp(extract(epoch from current_date));
to_timestamp
------------------------
2025-11-21 00:00:00-08
(1 row)

and this in 9.2.24:

regression=# set timezone = 'America/Los_Angeles';
SET
regression=# select to_timestamp(extract(epoch from current_date));
to_timestamp
------------------------
2025-11-20 16:00:00-08
(1 row)

regards, tom lane

I guess this reveals the age of the bit of code I was resurrecting, he says
while pulling out his Pg 8.4 release t-shirt. :)

After much more digging I found the relevant remark way back in the 9.2
release notes (https://www.postgresql.org/docs/release/9.2.0/):

Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch from
local midnight, not UTC midnight (Tom Lane)

This change reverts an ill-considered change made in release 7.3. Measuring
from UTC midnight was inconsistent because it made the result dependent on
the timezone setting, which computations for timestamp without time zone
should not be. The previous behavior remains available by casting the input
value to timestamp with time zone.

Sorry for the goose chase.

-Steve