postgresql 仅选择今天（从午夜开始）的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9716868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select today's (since midnight) timestamps only
提问by Alexander Farber
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):
我有一台带有 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
But comparing u.login > now()-interval '24 hour'also delivers the users before the last 01:00, which is bad, esp. in the mornings.
但是比较u.login > now()-interval '24 hour'也会在最后 01:00 之前交付用户，这很糟糕，尤其是。在早上。
Is there any efficient way to get the logins since the last 01:00without doing string acrobatics with to_char()?
是否有任何有效的方式来获取自上次01:00登录而不做字符串杂技与to_char()？
回答by Erwin Brandstetter
Inspired by @Frank's comment I ran some tests and adapted my query accordingly. This should be 1) correct and 2) as fast as possible:
受到@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;
As there are no future timestamps in your table (I assume), you need no upper bound.date_trunc('day', now())is almost the same as now()::date(or some other alternatives detailed below), only that it returns timestampinstead of a date. Both result in a timestampanyway after adding an interval.
由于您的表中没有未来的时间戳（我假设），因此您不需要上限。date_trunc('day', now())几乎与now()::date（或下面详述的其他一些替代方案）相同，只是它返回timestamp而不是 a date。这两种结果的timestamp反正添加之后interval。
Below expressions perform slightly differently. They yield subtly different results because localtimestampreturns data type timestampwhile now()returns timestamp with time zone. But when cast to date, either is converted to the same localdate, and a timestamp [without time zone]is presumed to be in the local time zone, too. So when compared to the corresponding timestamp with time zonethey all result in the same UTC timestamp internally. More details on time zone handling in this related question.
以下表达式的表现略有不同。他们产生微妙的不同的结果，因为localtimestamp返回的数据类型timestamp，而now()回报timestamp with time zone。但是当date转换为时，要么转换为相同的本地日期，并且 a 也timestamp [without time zone]被假定为在本地时区。因此，与相应的相比，timestamp with time zone它们都在内部产生相同的 UTC 时间戳。有关此相关问题中时区处理的更多详细信息。
Best of five. Tested with PostgreSQL 9.0. Repeated with 9.1.5: consistent results within 1 % error margin.
最好的五个。使用 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()::dateis obviously slightly faster than CURRENT_DATE.
now()::date显然比 略快CURRENT_DATE。
回答by Frank Bollack
An easy way of getting only time stamps for the current day since 01:00 is to filter with
CURRENT_DATE + interval '1 hour'
仅获取自 01:00 以来当天的时间戳的一种简单方法是过滤
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.
希望有帮助。
回答by Vivegan
select * from termin where DATE(dateTimeField) = '2015-11-17'
This works well for me!
这对我很有效！
回答by Suisse
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.
这对我有用 - 它选择今天日期的所有行。
回答by Clodoaldo Neto
where
u.login > u.logout
and
date_trunc('day', u.login) = date_trunc('day', now())
and
date_trunc('hour', u.login) >= 1
