postgresql 在两个时间戳之间选择记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8723523/
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
Selecting records between two timestamps
提问by Sharon
I am converting an Unix script with a SQL transact command to a PostgreSQL command.
我正在将带有 SQL transact 命令的 Unix 脚本转换为 PostgreSQL 命令。
I have a table with records that have a field last_update_time(xtime) and I want to select every record in the table that has been updated within a selected period.
我有一个包含字段last_update_time(xtime) 记录的表，我想选择表中已在选定时间段内更新的每条记录。
Say, the current time it 05/01/2012 10:00:00and the selected time is 04/01/2012 23:55:00. How do I select all the records from a table that have been updated between these dates. I have converted the 2 times to seconds in the Unix script prior to issuing the psql command, and have calculated the interval in seconds between the 2 periods.
说，当前时间它05/01/2012 10:00:00和选择的时间是04/01/2012 23:55:00。如何从这些日期之间更新的表中选择所有记录。在发出 psql 命令之前，我已在 Unix 脚本中将 2 次转换为秒，并计算了 2 个周期之间的间隔（以秒为单位）。
I thought something like
我想像
SELECT A,B,C FROM table
WHERE xtime BETWEEN now() - interval '$selectedtimeParm(in secs)' AND now();
I am having trouble evaluating the Parmfor the selectedtimeParm- it doesn't resolve properly.
我在评估Parmfor时遇到问题selectedtimeParm- 它没有正确解决。
Editor's note: I did not change the inaccurate use of the terms period, time frame, timeand datefor the datetime type timestampbecause I discuss that in my answer.
编者按：我没有改变不正确的使用条款period，time frame，time并date为DateTime类型timestamp，因为我讨论，在我的答案。
回答by Erwin Brandstetter
What's wrong with:
有什么问题：
SELECT a,b,c
FROM table
WHERE xtime BETWEEN '2012-04-01 23:55:00'::timestamp
AND now()::timestamp;
If you want to operate with a count of seconds as interval:
如果您想以秒为单位interval进行操作：
...
WHERE xtime BETWEEN now()::timestamp - (interval '1s') * $selectedtimeParm
AND now()::timestamp;
Note, how I used the standard ISO 8601 date format YYYY-MM-DD h24:mi:sswhich is unambiguous with any locale or DateStylesetting.
请注意，我如何使用标准 ISO 8601 日期格式YYYY-MM-DD h24:mi:ss，该格式与任何语言环境或DateStyle设置都没有歧义。
Note also, that the first value for the BETWEENconstruct must be the smaller one. If you don't know which value is smaller use BETWEEN SYMMETRICinstead.
另请注意，BETWEEN构造的第一个值必须是较小的值。如果您不知道哪个值较小，请BETWEEN SYMMETRIC改用。
In your question you refer to the datetime type timestampas "date", "time" and "period". In the title you used the term "time frames", which I changed that to "timestamps". All of these terms are wrong. Freely interchanging them makes the question even harder to understand.
在您的问题中，您将日期时间类型timestamp称为“日期”、“时间”和“期间”。在标题中，您使用了“时间框架”一词，我将其更改为“时间戳”。所有这些术语都是错误的。随意交换它们会使问题更难理解。
That, and the fact that you only tagged the question psql(the problem hardly concerns the command line terminal) might help to explain why nobody answered for days. Normally, it's a matter of minutes around here. I had a hard time understanding your question, had to read it a couple of times.
那一点，以及您只标记了问题psql（该问题几乎不涉及命令行终端）这一事实可能有助于解释为什么好几天没有人回答。通常，这里是几分钟的事情。我很难理解你的问题，不得不读了几遍。
You need to understand the data types date, interval, timeand timestamp- with or without time zone. Start by reading the chapter "Date/Time Types" in the manual.
你需要了解的数据类型date，interval，time和timestamp-有或没有时区。首先阅读手册中的“日期/时间类型”一章。
Error message would have gone a long way, too.
错误消息也有很长的路要走。
回答by user10078199
For anyone who is looking for the fix to this. You need to remove timestamp from the where clause and use BETWEEN!
对于正在寻找解决此问题的任何人。您需要从 where 子句中删除时间戳并使用 BETWEEN！
TABLENAME.COL-NAME-FOR-TIMESTAMP BETWEEN '2020-01-29 04:18:00-06' AND CURRENT_TIMESTAMP
