Re: Unexpected date conversion results

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(dot)klaver(at)aklaver(dot)com