使用PostgreSQL处理时间

Question

我有一个表格，其中包含一个日期和时间字段。我在处理这个表格时遇到了困难，部分原因是我不理解如何将时间转换为数字。我使用以下命令创建了一个表格：

CREATE TABLE tracking.time_record
(
  date date, 
  "time" time without time zone,
  id character(100)
)

我的数据示例如下：

"2012-04-18" | "18:33:19.612" | "2342342384" 

我该如何运行查询，以便查看在某一天时间值> 10 pm的所有id值？

我意识到，由于我的时间存储在字符类型变量中，所以像这样的查询无法正常工作:

SELECT * FROM tracking.time_record 
WHERE "time" > "04:33:38.884" AND date > "2012-04-18"

这是我第一次探索时间/日期跟踪 - 我可能应该选择不同的列名称。

- djq

---

为什么不将日期和时间合并成带/不带时区的时间戳？这样可以更容易地进行比较，如果需要，您总是可以提取相关部分。 - wildplasser

那是个好观点。现在我对时间戳的工作原理有了更好的理解，以后我会这样做。 - djq

Answer 1

迄今为止，两个答案都没有捕捉到你实际的问题。

虽然显式地将类型转换为适当的类型无疑是有益的，但不是必需的。PostgreSQL会自动将字符串文字强制转换为适当的类型。

您的问题源于基本语法错误：
双引号用于标识符："MyColumn" - 仅在名称非法（大小写混合，保留字等）时才需要使用，应尽量避免。
单引号用于值：'string literal'。

您可能会对PostgreSQL手册中关于标识符和常量的写作精美的章节感兴趣。

顺便说一句，永远不要使用date或time作为列名。这两个名字在每个SQL标准和PostgreSQL中都是保留字和类型名称。这将导致令人困惑的代码和错误消息。

我建议只使用单个timestamp列，而不是单独的date和time： 而您几乎肯定不希望使用character(100)作为数据类型，永远不要 - 尤其是对于id列。这种填充了空格的类型基本上只是出于历史原因才存在。考虑使用text或varchar代替：

• 使用数据类型“文本”存储字符串有什么缺点吗？

可能看起来像这样：

CREATE TABLE tbl (
   tbl_id text CHECK(length(id) <= 100)
 , ts timestamp
);

如果您只需要这些组件，请将其转换为time或date，这很简洁而且便宜:

SELECT ts::time AS the_time, ts::date AS the_date FROM tbl;

使用date_trunc()或extract()进行更具体的需求查询。
要查询...在某一天晚上10点后有时间值的id值:

SELECT *
FROM   tbl
WHERE  ts::time > '22:00'
AND    ts::date = '2012-04-18';

或者，对于任何连续的时间段：

...
WHERE  ts > '2012-04-18 22:00'::timestamp
AND    ts < '2012-04-19 00:00'::timestamp;

第二种形式可以更好地利用 ts 上的普通索引，在大表情况下会更快。

有关 PostgreSQL 中的 timestamp 处理的更多信息：

• 在Rails和PostgreSQL中完全忽略时区

- Erwin Brandstetter

---

顺便说一下，我怀疑您实际上不想用 character (100) 作为您的 id。这种填充空格的类型基本上只是出于历史原因而存在。考虑使用 text 或 varchar。 - Erwin Brandstetter

Answer 2

您可以使用 date_part 函数来获取小时数。

select id 
from tracking.time_record
where date_part('hour', time) >= 22
  and date = '2012-04-18'

“>=22”而不是“>22”，是为了捕捉晚上10点到11点之间的时间段。这也会捕捉到晚上10点整，但很容易调整查询以捕捉正确的时间段。

您可以在这里看到一个可行的示例。

- dcarneiro

---

date_part() 不是必需的。 "time" > '22:00' 就可以了。 - Erwin Brandstetter

Answer 3

比较时间和日期的值在postgresql中应该可以正常工作，只需要确保将字符串转换为适当的类型即可。

SELECT * FROM tracking.time_record 
WHERE "time" > time '04:33:38.884' AND "date" > date '2012-04-18'

- user1346466

---

请注意，此操作将在2012年4月18日上午4:33之后查找记录（因此不会返回该日期之前的任何内容，也不会返回任何一天早于上午4:33的记录）。您可能需要一个时间戳列。 - Andrew

强制类型转换（虽然没有伤害）在这种情况下并不是必要的。Postgres会自动将字符串字面量强制转换为匹配的类型。语法错误才是问题所在。 - Erwin Brandstetter

你说得对。我仍然建议进行类型转换。它是在比较字符串还是日期或其他什么？如果不查看表规范，你真的无法确定。 - user1346466