How do I specify “the start of today” in a specific time zone?

Question

I have a table with a “timestamp with time zone” column. I would like to find all of the rows whose timestamp is earlier than today, where “today” is determined in a specific time zone.

I know how to use at time zone to interpret a literal timestamp as being in some particular time zone, and I know how to use date_trunc to get the beginning of this day. But I’m not sure how to combine them to get what I need. I tried

select date_trunc('day', current_date at time zone 'cst');

which gave me “2015-03-16 00:00:00”, but it’s unclear to me what time zone is used for this result (or whether it has one at all). How can I select the beginning of the current day according to a specific time zone?

Given that my current day is 2015-03-17, how would you expect me to answer the question, "What is the beginning of the current day in UTC?" One possibility is "2015-03-16 20:00:00-04". Another is "2015-03-17 00:00:00-04". Look hard at this: `select current_date as today, current_date::timestamp as beg_of_today, current_date::timestamp at time zone 'UTC' as beg_of_today_utc, current_date + time '00:00:00' at time zone 'UTC';` — Mike Sherrill 'Cat Recall', Mar 18 '15 at 00:16

score 22 · Accepted Answer · answered Mar 18 '15 at 22:32

22

It helped me to reframe the question as follows: what are the current date and time in the Central time zone? Then, what I want is the midnight at the beginning of that day [in the Central time zone]. I found that I could write this as

current_date::timestamp AT TIME ZONE 'cst'

answered Mar 18 '15 at 22:32

bdesham

15,430
13
79
123

That answer was very helpful only want to add that this is a complete solution that doesn't need `date_trunc`. And it's equivalent to the more verbose `select date_trunc('day', current_date)::timestamp at time zone 'cst'; – lujop Oct 03 '21 at 20:29

score 1 · Answer 2 · answered Aug 29 '21 at 11:54

This example may be useful to understand PostgreSQL timezone functionality:

SELECT now()            as utc_now, -- 2021-08-29 11:38:08.552247 +00:00
       pg_typeof(now()) as type_of_utc_now, -- timestamp with time zone

       tehran_local_now, -- 2021-08-29 16:08:08.552247
       pg_typeof(tehran_local_now), -- timestamp without time zone

       utc_calculated_from_tehran_local_now, -- 2021-08-29 11:38:08.552247 +00:00
       pg_typeof(utc_calculated_from_tehran_local_now), -- timestamp with time zone

       tehran_local_start_of_today, -- 2021-08-29 00:00:00.000000
       pg_typeof(tehran_local_start_of_today), -- timestamp without time zone

       utc_start_of_today_at_tehran_timezone, -- 2021-08-28 19:30:00.000000 +00:00
       pg_typeof(utc_start_of_today_at_tehran_timezone) -- timestamp with time zone

FROM (SELECT now() at time zone 'Asia/Tehran' as tehran_local_now) as tln
         CROSS JOIN
     (SELECT (now() at time zone 'Asia/Tehran') at time zone 'Asia/Tehran' as utc_calculated_from_tehran_local_now) as ucftln
         CROSS JOIN
     (SELECT date_trunc('day', now() at time zone 'Asia/Tehran') as tehran_local_start_of_today) as tlsot
         CROSS JOIN
     (SELECT date_trunc('day', now() at time zone 'Asia/Tehran') at time zone
             'Asia/Tehran' as utc_start_of_today_at_tehran_timezone) as usotatt;

The PostgreSQL function now() (column utc_now) returns the current timestamp in UTC without any timezone. Its type is timestamp with time zone. Its Java equivalent is Instant.now().

When you use now() at time zone 'Asia/Tehran' (column tehran_local_now) it indicates the current date-time in Tehran local. Its type is timestamp without time zone. Its Java equivalent is:

Instant.now().atZone(ZoneId.of("Asia/Tehran")).toLocalDateTime()

When you use date_trunc('day', now() at time zone 'Asia/Tehran') (column tehran_local_start_of_today) it indicates the start of today in Tehran local. Its type is timestamp without time zone. Its Java equivalent is:

Instant.now().atZone(ZoneId.of("Asia/Tehran")).toLocalDate().atStartOfDay()

When you use date_trunc('day', now() at time zone 'Asia/Tehran') at time zone 'Asia/Tehran' (column utc_start_of_today_at_tehran_timezone) it indicates the start of today in Tehran timezone (not local). Its type is timestamp with time zone. You may need this one. Its Java equivalent is:

Instant.now().atZone(ZoneId.of("Asia/Tehran")).truncatedTo(DAYS).toInstant()

How do I specify “the start of today” in a specific time zone?

2 Answers2

Linked