如何在特定时区指定“今天的开始”？ [英] How do I specify “the start of today” in a specific time zone?

问题描述

我有一个带有带时区的时间戳列的表。我想找到所有时间戳都比今天早的行，其中今天是在特定时区中确定的。

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.

我知道如何使用时区来将文字时间戳解释为处于某个特定时区，并且我知道如何使用 date_trunc 来获取这一天的开始。但我不确定如何将它们结合起来以得到所需的东西。我尝试了

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');

这给了我 2015-03-16 00:00:00，但我不清楚此结果使用哪个时区（或是否有一个时区）。如何根据特定的时区选择当天的开始时间？

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?

推荐答案

它帮助我将问题重新构造为如下：中部时区的当前日期和时间是什么？然后，我想要的是当天开始的午夜（在中央时区）。我发现我可以这样写：

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'

这篇关于如何在特定时区指定“今天的开始”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！