public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Steve Crawford <[email protected]>
To: PG-General Mailing List <[email protected]>
Subject: Re: Unexpected date conversion results
Date: Fri, 21 Nov 2025 16:38:10 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEfWYyzvM4XcfKunhvT1_xs_9rGnbXbRvnn_znQD4-Wg-aA5Vg@mail.gmail.com>
References: <CAEfWYyzvM4XcfKunhvT1_xs_9rGnbXbRvnn_znQD4-Wg-aA5Vg@mail.gmail.com>

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]

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Unexpected date conversion results
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox