Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

postgresql - Select today's (since midnight) timestamps only

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()?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Inspired by @Frank's comment I ran some tests and adapted my query accordingly. This should be 1) correct and 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 returns timestamp instead of a date. Both result in a timestamp anyway 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 the same 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 margin.

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...