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

Erwin Brandstetter · Accepted Answer · 2022-01-31T01:41:16.523

92

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'

edited Jan 31 '22 at 01:41

answered Mar 15 '12 at 09:37

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you. Does a cast from date to timestamp cost anything? – Alexander Farber Mar 15 '12 at 10:15
@Erwin Brandstetter: But `date_trunc('day', now())` might cost even more, don't you think? – Frank Bollack Mar 15 '12 at 12:43
1

@Erwin Brandstetter: nice work and thanks for the effort, +1 from me. But out of curiosity, using the expression in question in a `WHERE` clause should be evaluated only once per query, right? – Frank Bollack Mar 18 '12 at 17:23
This looks like it will return no results between 0.00 and 1.00 local time- that's fine if we are looking for today's results since 1.00, but OP states he wants the results since that last 1.00. – cmc Jan 31 '22 at 00:17
1

@cmc: I added a fix for the corner case. Updated across the board while being at it. – Erwin Brandstetter Jan 31 '22 at 01:42
@ErwinBrandstetter Awesome! And clever, I'd used a CASE, which is always ugly, didn't test performance though. – cmc Feb 01 '22 at 09:12

score 20 · Answer 2 · answered Jul 04 '15 at 17:37

20

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.

answered Jul 04 '15 at 17:37

Suisse

3,467
5
36
59

16

Why not just `select * from termin where DATE(dateTimeField) = CURRENT_DATE`? – Markus Rautopuro Aug 14 '18 at 08:38

score 20 · Answer 3 · answered Nov 17 '15 at 07:57

20

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

This works well for me!

answered Nov 17 '15 at 07:57

Vivegan

201
2
3

Frank Bollack · Answer 4 · 2012-03-15T12:44:29.367

15

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.

edited Mar 15 '12 at 12:44

answered Mar 15 '12 at 09:34

Frank Bollack

24,478
5
49
58

score 4 · Answer 5 · answered Mar 15 '12 at 09:29

4

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

answered Mar 15 '12 at 09:29

Clodoaldo Neto

118,695
26
233
260

If `u.login` is string, just add `::date` after it. – qräbnö Oct 23 '20 at 15:22

cmc · Answer 6 · 2022-02-01T09:13:35.680

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.

Select today's (since midnight) timestamps only

6 Answers6

Linked