如何使用psycopg2 (PostgreSQL)比较日期

Question

通过使用PostgreSQL Python模块psycopg2，我想找到结束时间落在某个时间戳之前的行。我试图用… where end_time < %s的SQL查询上的cursor.execute(…)来做到这一点，其中%s被带时区的datetime.datetime对象替换。psycopg2生成的SQL是:

select * from my_table where end_time < '2014-05-01T13:00:00+00:00'::timestamptz;

返回如下行:

id  |     start_time      |      end_time       
331 | 2014-05-01 14:55:00 | 2014-05-01 15:05:00

结束时间不早于2014-05-01 13:00 UTC，与我期望的相反。

在psycopg2中执行所需时间选择的正确方法是什么?

Answer 1

好的，使用PostgreSQL时间戳和时区在表解决了这个问题。

下面是问题所在的详细信息:

• 我将时区感知的时间戳放在不具有时区感知的列中。我假设timestamp without time zone列将包含UTC时间(如timestamp with time zone)，但它实际上包含本地时间中的时间戳，如文档中所述:

  不带时区的时间戳与带时区的时间戳之间的转换时区通常假定时间戳没有时区值应采用或给出时区本地时间。不同的时区可以使用AT TIME ZONE指定转换时间。

  因此，00:00 UTC在中国时被存储为08:00在timestamp without time zone列中。

• 当做比较时，如"timestamp 没有时区" <"timestamp with time zone"，我在文档中找不到PostgreSQL做什么。然而，在这个问题中观察到的情况与首先以相同的方式删除时区的想法是一致的，然后比较没有时区的时间戳。我不应该对结果感到惊讶:在中国(UTC+8)，晚时间"15:00"确实比UTC时间13:00 早。关键是要知道显示(和存储)的时间是在当地时区。

我从中得到的启示是:带时区和不带时区的时间戳之间的转换最好显式地处理。

Answer 2

指定显示时间的时区。如果start_time和end_time是时间戳，没有时区，那么你还需要告诉postgresql它们应该在

中的哪个时区。

select 
    id,
    start_time at time zone 'UTC' at time zone 'UTC',
    end_time at time zone 'UTC' at time zone 'UTC'
from my_table 
where end_time at time zone 'UTC' < %s