Select today's (since midnight) timestamps only
PostgresqlDateDatetimeTimestampPostgresql 8.4Postgresql Problem Overview
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()
?
Postgresql Solutions
Solution 1 - Postgresql
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:
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'
Solution 2 - Postgresql
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 3 - Postgresql
select * from termin where DATE(dateTimeField) = '2015-11-17'
This works well for me!
Solution 4 - Postgresql
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 5 - Postgresql
where
u.login > u.logout
and
date_trunc('day', u.login) = date_trunc('day', now())
and
date_trunc('hour', u.login) >= 1
Solution 6 - Postgresql
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.