postgresql - 如何在 postgreSQL 中获取最近 24 小时的数据

Question

我想获取过去 24 小时的数据。我在 postgreSQL 中写了一个查询如下。但我无法如我所料得到答案。

 SELECT startdate::timestamp AS startdate,
    (DATE_PART('hour',startdate::timestamp)::integer) as hrs,count(guorderid)
    FROM ord_entitlement
    WHERE DATE_PART('Day',CURRENT_DATE::timestamp - startdate::timestamp) < 1
    AND DATE_PART('hour',startdate::timestamp) <= 24
    GROUP BY  hrs,startdate
    ORDER BY startdate

Answer 1

不是检查日期部分，而是计算时间以获得间隔。使用 NOW() 获取时间戳。

SELECT startdate::timestamp AS startdate,
      (DATE_PART('hour',startdate::timestamp)::integer) as hrs, 
      count(guorderid)
FROM ord_entitlement
WHERE NOW() > startdate::timestamptz
  AND NOW() - startdate::timestamptz <= interval '24 hours'
GROUP BY hrs,startdate
ORDER BY startdate

这可确保无论您所在的时区或夏令时如何，您都可以获得最后 24 小时的时间。 NOW() > startdate::timestamptz 确保您不会不小心从 future 获取东西。