选择两个时间戳之间的记录 [英] Selecting records between two timestamps

问题描述

我正在使用SQL transact命令将Unix脚本转换为PostgreSQL命令。

我有一个带有记录的表，其中包含一个字段 last_update_time （xtime），我想选择表中已被更新的所有记录。

说，当前时间 05/01/2012 10:00:00 ，所选时间是 04/01/2012 23:55:00 。如何从这些日期之间更新的表中选择所有记录。在发出psql命令之前，我已经在Unix脚本中将2次转换为秒，并且计算了两个周期之间的间隔（以秒为单位）。

我以为像

  SELECT A，B，C FROM table 
 WHERE xtime BETWEEN now（） -  interval'$ selectedtimeParm（in secs ）' 现在（）; 
  

我无法评估 Parm selectedtimeParm - 它无法正确解析。

_{编者按：我没有更改不准确使用术语期间，时间框架，时间和 date for datetime type timestamp 因为我在回答中讨论了。}

解决方案

有什么问题：

  b，c 
 FROM table 
 WHERE xtime BETWEEN'2012-04-01 23:55:00':: timestamp 
 AND now（）:: timestamp; 
  

如果要使用秒数计算间隔 ：

  ... 
 WHERE xtime BETWEEN now（） ：timestamp  - （interval'1s'）* $ selectedtimeParm 
 AND now（）:: timestamp;注意，我如何使用标准的ISO 8601日期格式 YYYY-MM- DD h24：mi：ss 这与任何区域设置无关，或  DateStyle  设置。
 
 
 
另请注意，第一个BETWEEN构造的值必须是较小的。如果您不知道哪个值较小，请使用  
 
 
 
在你的问题中，你引用日期时间类型 timestamp 为日期，时间和期间。在标题中，您使用了时间框架这个术语，我将其改为时间戳。所有这些术语都是错误的。自由交换它们使得这个问题更难于理解。
 
 
 
那个，你只是标记了问题 psql  
 
 
您需要了解数据类型 date ，间隔，时间和时间戳有或没有时区。请阅读手册中的日期/时间类型一章>。
 
 
 
错误信息也会有很长的路要走。
 
I am converting an Unix script with a SQL transact command to a PostgreSQL command.

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. 

Say, the current time it 05/01/2012 10:00:00 and 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.

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 Parm for the selectedtimeParm - it doesn't resolve properly.

Editor's note: I did not change the inaccurate use of the terms period, time frame, time and date for the datetime type timestamp because I discuss that in my answer.
解决方案
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:
...
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:ss which is unambiguous with any locale or DateStyle setting.

Note also, that the first value for the BETWEEN construct must be the smaller one. If you don't know which value is smaller use BETWEEN SYMMETRIC instead.

In your question you refer to the datetime type timestamp as "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.

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.

You need to understand the data types date, interval, time and timestamp - with or without time zone. Start by reading the chapter "Date/Time Types" in the manual.

Error message would have gone a long way, too.

                        
这篇关于选择两个时间戳之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！