选择今天（从午夜）时间戳 [英] Select today's (since midnight) timestamps only

问题描述

我有一台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和
 u.login> now（） -  interval '24 hours'
 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 |谢尔盖
  

但是比较 u.login> now（） - 间隔'24小时'也可以在上一个01:00之前递送用户，这是不好的，尤其是。

有没有任何有效的方法来从上次01:00 获得登录，而不用<$ c $的字符串杂技术c> to_char（）？

解决方案

灵感来自@ Frank的评论，我进行了一些测试，查询相应。这应该是1）正确2）尽可能快：

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

由于表中没有未来的时间戳（我假设），您无需上限。 br>
date_trunc（'day'，now（））几乎和 now（）:: date （或下面详细列出的一些其他替代方案），只是它是一个时间戳而不是日期。在添加间隔后，两者都会导致时间戳。

---

以下表达式略有不同。它们产生微妙的不同结果，因为 localtimestamp 返回数据类型 timestamp ，而 now（）返回 带时区的时间戳记 。但是，当转换为 date 时，或者转换为本地日期，而时间戳[无时区] 也被推定为在当地时区。所以当与具有时区的相应的时间戳相比时，它们都在内部产生相同的UTC时间戳。有关相关问题的时区处理的详细信息。

五分之一。用PostgreSQL 9.0测试。与9.1.5重复：1％错误内的一致结果。

  SELECT localtimestamp :: date + interval'1h' -  Total运行时间：351.688 ms 
，current_date + interval'1h' - 总运行时间：338.975 ms 
，date_trunc（'day'，now（））+ interval'1h' - 总运行时：333.032 ms 
，now（）:: date + interval'1h' - 总运行时间：278.269 ms 
 FROM generate_series（1，100000）
  

now（）:: date 明显比 CURRENT_DATE 。

I have a server with PostgreSQL 8.4 which is being rebooted every night at 01:00 (don't ask) and need to get a list of connected users (i.e. their timestamps are 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

But comparing u.login > now()-interval '24 hour' also delivers the users before the last 01:00, which is bad, esp. in the mornings.

Is there any efficient way to get the logins since the last 01:00 without doing string acrobatics with to_char()?

解决方案

Inspired by @Frank's comment I ran some tests and adapted my query accordingly. This should be 1) correct 2) as fast as possible:

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

As there are no future timestamps in your table (I assume), you need no upper bound.
date_trunc('day', now()) is almost the same as now()::date (or some other alternatives detailed below), only that it's a timestamp instead of a date. Both result in a timestamp after adding an interval.

---

Below expressions perform slightly differently. They yield subtly different results because localtimestamp returns data type timestamp while now() returns timestamp with time zone. But when cast to date, either is converted to same the local date, and a timestamp [without time zone] is presumed to be in the local time zone, too. So when compared to the corresponding timestamp with time zone they all result in the same UTC timestamp internally. More details on time zone handling in this related question.

Best of five. Tested with PostgreSQL 9.0. Repeated with 9.1.5: consistent results within 1% error.

SELECT localtimestamp::date     + interval '1h'  -- Total runtime: 351.688 ms
     , current_date             + interval '1h'  -- Total runtime: 338.975 ms
     , date_trunc('day', now()) + interval '1h'  -- Total runtime: 333.032 ms
     , now()::date              + interval '1h'  -- Total runtime: 278.269 ms
FROM   generate_series (1, 100000)

now()::date is obviously slightly faster than CURRENT_DATE.

这篇关于选择今天（从午夜）时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！