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

Question

У меня есть сервер с 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()?

Answer 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,

Answer 2

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

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

Answer 3

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

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

Answer 4

Простой способ получить только отметки времени для текущего дня с 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;

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

Answer 5

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

Answer 6

Все ответы до сих пор неверны, потому что они дают неправильный ответ между 0,00 и 1,00. Поэтому, если вы запустите запрос в этот период времени, вы не получите никаких результатов. Основываясь на ответе @ErwinBrandstetter, вам нужно следующее:

      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;

Я хотел бы обойтись без условного, но не нашел способа.

Редактировать: @ErwinBrandstetter сделал это без условного выражения, оставив это здесь для полноты картины.