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.