Select today's (since midnight) timestamps onl

问题:

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

回答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.

回答2:

An easy way of getting only time stamps for the current day since 01:00 is to filter with CURRENT_DATE + interval '1 hour'

So your query should look like this:

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;

Hope that helps.

回答3:

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

This works for me - it selects ALL rows with todays Date.

回答4:

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

This works well for me!

回答5:

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