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

Question

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

Answer 1

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.