Postgresql WHERE timea=timeb，如何指定精度

Question

我正在做一些匹配不同表中时间序列数据的工作。例如，该表有1秒时间序列的列和一个"最近的小时"列，如：

"datetime"                  "closesthourdatetime"
"01/01/2005 00:30:00"       "01/01/2005 01:00:00"
"01/01/2005 00:30:01"       "01/01/2005 01:00:00"
"01/01/2005 00:30:02"       "01/01/2005 01:00:00"

如果我运行

    SELECT time_series_only.closesthourdatetime
    FROM measdata.time_series_only
    WHERE time_series_only.datetime = time_series_only.closesthourdatetime

我没有得到我所希望的所有结果，不像运行

  SELECT DISTINCT time_series_only.closesthourdatetime
  FROM measdata.time_series_only

我在哪里。

我猜这是因为pgsql日期时间实际上是数字值，时间是一天的一部分，所以毫秒可能不完全匹配，我不在乎。

有没有一种方法可以查询，但指定最接近的秒或分钟其中time_series_only.datetime=time_series_only.closesthourdatetime没有像这样复杂且可能很慢的查询？：

  SELECT DISTINCT time_series_only.closesthourdatetime
  FROM measdata.time_series_only
  --WHERE 
  --time_series_only.datetime = time_series_only.closesthourdatetime
  /*EXTRACT (YEAR FROM time_series_only.datetime)   = EXTRACT(YEAR FROM time_series_only.closesthourdatetime)
  AND EXTRACT (MONTH FROM time_series_only.datetime)    = EXTRACT(MONTH FROM time_series_only.closesthourdatetime)
  AND EXTRACT (DAY FROM time_series_only.datetime)  = EXTRACT(DAY FROM time_series_only.closesthourdatetime)
  AND EXTRACT (HOUR FROM time_series_only.datetime)     = EXTRACT(HOUR FROM time_series_only.closesthourdatetime)
  AND EXTRACT (MINUTE FROM time_series_only.datetime) = EXTRACT(MINUTE FROM time_series_only.closesthourdatetime)
  AND EXTRACT (SECOND FROM time_series_only.datetime) = EXTRACT(SECOND FROM time_series_only.closesthourdatetime)

Answer 1

使用where子句可以获得预期结果：

WHERE datetime >= closesthourdatetime - INTERVAL '30 MINUTE'
AND   datetime <  closesthourdatetime + INTERVAL '30 MINUTE'

这里closesthourdatetime 01/01/2005 01:00:00将匹配范围[01/01/2005 00:30:00, 01/01/2005 01:30:00)中的所有日期时间，其中第二个日期是排他的。

测试/边缘案例的SQL Fiddle