sql - 使用 PostgreSQL 处理时间

Question

我有一个包含date 和time 字段的表格。我很难理解我是如何处理这个问题的，部分原因是我不明白如何将时间转换为数字。我使用以下命令创建了一个表:

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"

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

Answer 1

到目前为止，这两个答案都没有解决您的实际问题。

虽然显式转换为适当的类型肯定不会造成伤害，但没有必要。 PostgreSQL 自动将字符串文字强制转换为适当的类型。

您的问题源于基本语法错误:
双引号用于identifiers:"MyColumn" - 只有非法标识符(混合大小写，保留字，..)才需要避免开始。
单引号用于值:'string literal'。

您可能对 identifiers 上写得很好的章节感兴趣和 constants PostgreSQL 手册。

当我们这样做时，从不使用date 或time 作为列名。两者都是 reserved words在 PostgreSQL 中的每个 SQL 标准和类型名称中。这将导致困惑的代码和错误消息。

我建议只使用一个 timestamp 列而不是单独的 date 和 time:而且您几乎肯定不希望将 character(100) 作为数据类型，永远 - 特别是 id 列。这种空白填充类型基本上只是出于历史原因。考虑使用 text 或 varchar 代替:

• Any downsides of using data type "text" for storing strings?

可能看起来像这样:

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 中的时间戳 处理的更多信息:

• Ignoring timezones altogether in Rails and PostgreSQL