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

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 >= now()::date + interval '1h' AND    u.login > u.logout ORDER  BY u.login; 

As there are no future timestamps in your table (I assume), you need no upper bound.

Some equivalent expressions:

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

now()::date used to perform slightly faster than CURRENT_DATE in older versions, but that's not true any more in modern Postgres. But either is still faster than LOCALTIMESTAMP in Postgres 14 for some reason.

date_trunc('day', now()) + interval '1h' slightly differs in that it returns timestamptz. But it is coerced to timestamp according to the timezone setting of the current session in comparison to the timestamp column login, doing effectively the same.

See:

• Ignoring time zones altogether in Rails and PostgreSQL

---

To return rows for the previous day instead of returning nothing when issued between 00:00 and 01:00 local time, use instead:

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