使用 bigint 日期时间选择早于 X 天的记录

发布于 2024-12-19 22:52:09 字数 487 浏览 2 评论 0原文

我的表中将日期存储为 bigint，并且我正在尝试选择超过 30 天的记录。我在 SO 和 Google 上看到了大量与这个问题相关的问题，但我没有找到我需要的东西。

这是我所拥有的，这似乎非常低效：

SELECT
  COUNT(*) 
FROM 
  alert
WHERE 
  ('1969-12-31 19:00:00 GMT'::timestamp + (alert.mytstamp::text)::interval) < (localtimestamp - INTERVAL '30 days')

根据我的理解，它将 bigint mytstamp 字段转换为时间戳，以便将其与“30 天前”时间戳进行比较。它对表中的每条记录执行此操作:(。将当前时间戳 - 30 天转换为 bigint 一次，然后将其与我所有的 bigint 日期进行比较似乎更有效。

我的 SQL 技能很弱，所以放轻松：）。感谢您的帮助。

原文

I have dates stored as bigint in my table, and I'm trying to select records older than 30 days. I have seen a ton of questions on SO and Google related to this question, but I'm not finding what I need.

Heres what I have, which seems very inefficient:

SELECT
  COUNT(*) 
FROM 
  alert
WHERE 
  ('1969-12-31 19:00:00 GMT'::timestamp + (alert.mytstamp::text)::interval) < (localtimestamp - INTERVAL '30 days')

From what I understand, it is converting the bigint mytstamp field to a timestamp in order to compare it to the "30 days ago" timestamp. It does this for every record in the table :(. It seems more efficient to convert the current time stamp - 30 days to a bigint ONE TIME, then compare that to all my bigint dates.

My SQL skills are weak, so go easy :). Thanks for the help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空气里的味道 2024-12-26 22:52:09

我在发布到SO后再次找到了解决方案。我想这是一个幸运符。不管怎样，这似乎就是我正在寻找的，并且效率更高：

SELECT
  COUNT(*) 
FROM 
  alert
WHERE 
  alert.timestamp < extract('epoch' from (CURRENT_TIMESTAMP  - INTERVAL '10 days'))::bigint

我想知道 postgres 是否计算 extract('epoch' from (CURRENT_TIMESTAMP - INTERVAL '10 days')):: bigint 一次，或者每次记录比较。

Once again I found a solution right after posting to SO. Its a good luck charm I guess. Anyway, this seems to be what I'm looking for, and is much more efficient:

SELECT
  COUNT(*) 
FROM 
  alert
WHERE 
  alert.timestamp < extract('epoch' from (CURRENT_TIMESTAMP  - INTERVAL '10 days'))::bigint

I'm wondering if postgres does the calculation for extract('epoch' from (CURRENT_TIMESTAMP - INTERVAL '10 days'))::bigint once, or for every record comparison though.

回复收藏 0 原文

~没有更多了~