postgresql - Selecting records between two timestamps

Question

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.}

Answer 1

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.