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

postgresqldatedatetimetimestamppostgresql-8.4


Solution 1:[1]

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.

Solution 2:[2]

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

This works well for me!

Solution 3:[3]

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.

Solution 4:[4]

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

Solution 5:[5]

All answers so far are incorrect because they give the wrong answer between 0.00 and 1.00. So if you happen to run the query in that time period you get no results. Based on @ErwinBrandstetter's answer, what you want is this:

WHERE u.login > u.logout
AND u.login >= CASE WHEN NOW()::time < '1:00'::time THEN NOW()::date - INTERVAL '23 HOUR' ELSE NOW()::date + INTERVAL '1 HOUR' END;

I would love to do without the conditional but found no way to.

Edit: @ErwinBrandstetter did do it without a conditional, leaving this here for completeness.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Suisse
Solution 2 Vivegan
Solution 3
Solution 4 Clodoaldo Neto
Solution 5

Related Questions