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?

Answer 1

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'