Выберите сегодня (с полуночи) только отметки времени

У меня есть сервер с PostgreSQL 8.4, который перезагружается каждую ночь в 01:00 (не спрашивайте), и вам нужно получить список подключенных пользователей (т.е. их метки времени 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

Но сравнение u.login > now()-interval '24 hour' также доставляет пользователей до последнего 01:00, что плохо, особенно. по утрам.

Есть ли эффективный способ получить логины с последнего 01:00, не выполняя строковые акробатики с помощью to_char()?

Ответ 1

Вдохновленный комментарием @Frank, я провел несколько тестов и соответствующим образом адаптировал свой запрос. Это должно быть 1) правильно и 2) как можно быстрее:

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;

Поскольку в вашей таблице нет будущих временных меток (я полагаю), вам не нужна верхняя граница.
date_trunc('day', now()) почти то же самое, что и now()::date (или некоторые другие альтернативы, подробно описанные ниже), только если он возвращает timestamp вместо date. Оба результата приводят к timestamp в любом случае после добавления interval.

Ниже выражения выполняются несколько иначе. Они дают тонко разные результаты, потому что localtimestamp возвращает тип данных timestamp, а now() возвращает timestamp with time zone. Но при нажатии на date либо конвертируется в ту же локальную дату, и считается, что timestamp [without time zone] находится в локальном часовом поясе. Таким образом, по сравнению с соответствующим timestamp with time zone все они приводят к тому же самому временному интервалу UTC. Подробнее об обработке часового пояса в этом связанном вопросе.

Лучший из пяти. Протестировано с PostgreSQL 9.0. Повторяется с 9.1.5: согласованные результаты с погрешностью в 1%.

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, очевидно, немного быстрее, чем CURRENT_DATE.

Ответ 2

Легкий способ получить только отметки времени для текущего дня с 01:00 - это фильтр с CURRENT_DATE + interval '1 hour'

Итак, ваш запрос должен выглядеть так:

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;

Надеюсь, что это поможет.

Ответ 3

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

Это хорошо работает для меня!

Ответ 4

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

Ответ 5

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

Это работает для меня - он выбирает ВСЕ строки с текущей датой.