sql - WHERE expiry_date > (current_date - 30 * interval '1 day')

Question

我正在尝试仅 SELECT 行，其中 expiry_date(类型为带时区的时间戳)不超过 current_date 30 天(即到期日为 30 天) .

我原以为这行得通(注意:写一个更好的测试用例!)但显然不行。 CLI 中的某些测试未返回预期结果。

第一个查询抓取单行(详细信息页面)，第二个查询抓取多行(分页 View )。

SELECT *
FROM listings
WHERE id = $1 AND expiry_date > (current_date - $2 * interval '1 day')

(给定一个 ID($1)，如果到期日期不超过 30($2)天，则返回该行)。

SELECT *
FROM listings
WHERE expiry_date > (current_date - $1 * interval '1 day')
ORDER BY expiry_date DESC OFFSET $2 LIMIT $3

(给定一个偏移量($2)，返回未过期的行，最大为 limit($3)。在默认情况下，它们分别为 0 和 15。

• 在过去的 expiry_date(即 2014-03-08 05:15:44.236747+00)行上运行第一个查询时，它仍然返回该行

  SELECT id, expiry_date FROM listings where id = '96144752ad41b6bf' AND expiry_date > (current_date - 30 * interval '1 day');
  ____________________________________________________
  _        id        _          expiry_date          _
  ____________________________________________________
  _ 96144752ad41b6bf _ 2014-03-09 02:33:25.855162+00 _
  ____________________________________________________
  (1 row)
  

• 运行第二个查询时，我混合了过期行和未过期行。

  SELECT id, expiry_date FROM listings WHERE expiry_date > (current_date - 30 * interval '1 day') ORDER BY expiry_date DESC OFFSET 0 LIMIT 15;
  ____________________________________________________
  _        id        _          expiry_date          _
  ____________________________________________________
  _ 333b0291f8e357b9 _ 2014-04-04 21:40:14.744937+00 _
  _ 975d2a671ab577dd _ 2014-03-26 03:47:21.872271+00 _
  _ af35cbc3b2f6189b _ 2014-03-25 04:43:23.78521+00  _
  _ 3c3658ecceafde4b _ 2014-03-23 14:24:08.023696+00 _
  _ 931c2d5795705b5b _ 2014-03-10 15:43:52.05335+00  _
  _ eddb3711f02a0ad0 _ 2014-03-10 02:38:53.754079+00 _
  _ e2814251618590db _ 2014-03-09 14:06:07.829742+00 _
  _ 96144752ad41b6bf _ 2014-03-09 02:33:25.855162+00 _
  _ 1dbdcd6cb2cc6e7f _ 2014-03-08 05:58:52.121108+00 _
  _ d25d1b06b9e69f3e _ 2014-03-08 05:52:37.887371+00 _
  _ c52aa23d79583033 _ 2014-03-08 05:17:09.683484+00 _
  _ 56e9f4cdca899a40 _ 2014-03-08 05:15:44.236747+00 _
  _ 302f0226b7b33f05 _ 2014-03-08 05:09:43.909115+00 _
  _ edc45b7ca32f69f5 _ 2014-03-07 14:13:51.366852+00 _
  _ 90c1ef396073ff28 _ 2014-03-07 13:04:12.250061+00 _
  ____________________________________________________
  (15 rows)
  

对于上下文，日期在我的网络应用程序中生成为 UTC 时间戳；通过将 X 天(通常为 30)添加到 created_date 时间戳，在插入之前设置 expiry_date。

我在这里错过了什么？

Answer 1

结果正是我所期望的。

http://sqlfiddle.com/#!15/d110a/3

我认为您的问题出在查询的定义中。你说:

Where the expiry_date (of type timestamp with timezone) is no more than 30 days less than the current_date (i.e. the expiry date is 30 days).

这还不是很清楚。你到底想要什么？不到三十天过期的行？未过期但将在 30 天内过期的行？不会在 30 天内过期的行？

您当前的查询查找已过期或将在 30 天内过期的行，即当前日期减去三十天小于过期日期。

regress=> SELECT current_date,  (current_date - 30 * interval '1 day'), expiry_date
regress-> FROM listings
regress-> WHERE id = '96144752ad41b6bf';
    date    |      ?column?       |          expiry_date          
------------+---------------------+-------------------------------
 2014-03-19 | 2014-02-17 00:00:00 | 2014-03-09 10:33:25.855162+08
(1 row)