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

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


postgresql date timestamp datetime postgresql-8.4
person Alexander Farber    schedule 15.03.2012    source источник

Ответы (5)


arrow_upward
68
arrow_downward

Вдохновленный комментарием @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 .

person Erwin Brandstetter    schedule 15.03.2012
comment
Спасибо. Приведение от даты к временной метке чего-нибудь стоит? - person Alexander Farber; 15.03.2012
comment
@Erwin Brandstetter: Но date_trunc('day', now()) может стоить еще дороже, тебе не кажется? - person Frank Bollack; 15.03.2012
comment
@Erwin Brandstetter: отличная работа и спасибо за усилия, +1 от меня. Но из любопытства использование рассматриваемого выражения в предложении WHERE должно оцениваться только один раз для каждого запроса, верно? - person Frank Bollack; 18.03.2012
comment
@FrankBollack: Да, если функция объявлена ​​как STABLE или IMMUTABLE, планировщик запросов использует информацию и оценивает только один раз. Все функции в моем ответе попадают в эту категорию. Они возвращают одно и то же значение для одной транзакции. Это отличается для функций statement_timestamp() или clock_timestamp(). Они меняются в транзакции (но я не думаю, что они меняются в одном операторе). Вы найдете их все по ссылке, которую я дал в конце моего поста. - person Erwin Brandstetter; 19.03.2012

arrow_upward
15
arrow_downward 

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

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

person Suisse    schedule 04.07.2015
comment
Почему бы не просто select * from termin where DATE(dateTimeField) = CURRENT_DATE? - person Markus Rautopuro; 14.08.2018

arrow_upward
13
arrow_downward

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

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

person Frank Bollack    schedule 15.03.2012

arrow_upward
13
arrow_downward 

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

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

person Vivegan    schedule 17.11.2015

arrow_upward

arrow_downward

person    schedule
comment
Если u.login является строкой, просто добавьте ::date после нее. - person qräbnö; 23.10.2020