仅选择今天（从午夜以来）的时间戳

Question

我有一个带有PostgreSQL 8.4的服务器，每晚01:00会重新启动(不要问为什么)，我需要获取连接用户的列表（即他们的时间戳为u.login > u.logout）：

SELECT u.login, u.id, u.first_name
FROM pref_users u
WHERE u.login > u.logout and 
      u.login > now() - interval '24 hour'
ORDER BY u.login;

           login            |           id   | first_name
----------------------------+----------------+-------------
 2012-03-14 09:27:33.41645  | OK171511218029 | Alice
 2012-03-14 09:51:46.387244 | OK448670789462 | Bob
 2012-03-14 09:52:36.738625 | OK5088512947   | Sergej

但是比较u.login > now()-interval '24小时'也会返回在上一个01:00之前登录的用户，这很糟糕，尤其是在早上。

有没有一种有效的方法可以获取自上一个01:00以来的登录记录而不使用to_char()进行字符串操作？

- Alexander Farber

Answer 1

这个应该是1)正确的 2)尽可能快:

SELECT u.login, u.id, u.first_name
FROM   pref_users u
WHERE  u.login >= now()::date + interval '1h'
AND    u.login > u.logout
ORDER  BY u.login;

假设您的表中没有未来的时间戳（我猜测），那么您就不需要上限。

一些等价的表达式：

SELECT localtimestamp::date     + interval '1h'
     , current_date             + interval '1h'
     , date_trunc('day', now()) + interval '1h'
     , now()::date              + interval '1h'

now()::date 相对于旧版本的Postgres来说速度略快于 CURRENT_DATE，但在现代Postgres中不再成立。然而两者仍然比Postgres 14的LOCALTIMESTAMP更快，原因未知。

date_trunc('day', now()) + interval '1h' 稍微有所不同，它返回 timestamptz。但是根据当前会话的 timezone 设置与 timestamp 列 login 进行比较时被强制转换为 timestamp，从而实际上执行相同的操作。

参见：

• 完全忽略Rails和PostgreSQL中的时区

---

要返回前一天的行，而不是在当地时间00:00至01:00之间返回空值，请改用:

WHERE  u.login >= (LOCALTIMESTAMP - interval '1h')::date + interval '1h'

- Erwin Brandstetter

---

谢谢。从日期到时间戳的转换需要付出什么代价吗？ - Alexander Farber

@Erwin Brandstetter：但是date_trunc('day', now())可能会更耗费时间，你觉得呢？ - Frank Bollack

@Erwin Brandstetter：干得好，感谢您的努力，我给你点赞。但是出于好奇，使用问题中的表达式在WHERE子句中应该只被查询一次，对吗？ - Frank Bollack

这看起来似乎不会返回任何0.00到1.00当地时间之间的结果——如果我们正在寻找自1.00以来的今天的结果，那么这很好，但OP声明他想要自上次1.00以来的结果。 - cmc

@cmc：我为这个特殊情况添加了一个修复。顺便一提，我已经全面更新了它。 - Erwin Brandstetter

@ErwinBrandstetter 太棒了！而且聪明，我之前用的是CASE，总是很丑陋，不过没有测试过性能。 - cmc

Answer 2

select * from termin where DATE(dateTimeField) = '2015-11-17'

这对我很有效！

- Vivegan

Answer 3

select * from termin where DATE(dateTimeField) >= CURRENT_DATE AND DATE(dateTimeField) < CURRENT_DATE + INTERVAL '1 DAY'

对我很有效-它选择今天日期的所有行。

- Suisse

---

为什么不直接使用select * from termin where DATE(dateTimeField) = CURRENT_DATE？ - Markus Rautopuro

Answer 4

获取当前日期从01:00开始的时间戳的简单方法是使用过滤器： CURRENT_DATE + interval '1 hour'

因此，您的查询应该如下所示：

SELECT u.login, u.id, u.first_name
FROM pref_users u
WHERE u.login > u.logout AND
      u.login > CURRENT_DATE + interval '1 hour'
ORDER BY u.login;

希望这可以帮到您。

- Frank Bollack

Answer 5

where 
    u.login > u.logout 
    and     
    date_trunc('day', u.login) = date_trunc('day', now()) 
    and 
    date_trunc('hour', u.login) >= 1

- Clodoaldo Neto

---

如果 u.login 是字符串，只需在其后添加 ::date。 - qräbnö

Answer 6

到目前为止，所有的答案都是不正确的，因为它们在0.00和1.00之间给出了错误的答案。因此，如果你碰巧在那个时间段运行查询，你将得不到任何结果。根据@ErwinBrandstetter的答案，你需要这样做：

WHERE u.login > u.logout
AND u.login >= CASE WHEN NOW()::time < '1:00'::time THEN NOW()::date - INTERVAL '23 HOUR' ELSE NOW()::date + INTERVAL '1 HOUR' END;

我很想不使用条件语句，但是找不到方法。

编辑：@ErwinBrandstetter确实做到了不使用条件语句，为了完整性将此留在这里。

- cmc