40

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Sharon
  • 401
  • 1
  • 4
  • 5

2 Answers2

78

This works:

SELECT a, b, c
FROM   tbl
WHERE  xtime BETWEEN timestamp '2012-04-01 23:55:00' AND LOCALTIMESTAMP;

LOCALTIMESTAMP being the SQL standard way of saying now()::timestamp.

Note the standard ISO 8601 format YYYY-MM-DD HH24:MI:SS which is unambiguous for timestamps with any locale or DateStyle setting.

The first value for BETWEEN must be the smaller one. If you don't know what to expect, use BETWEEN SYMMETRIC instead. (But that can result in a less efficient query plan.)

However, Postgres timestamps aren't discrete values, and BETWEEN includes lower and upper bound. Rather avoid BETWEEN with timestamps and use this instead:

SELECT a, b, c
FROM   tbl
WHERE  xtime >= timestamp '2012-04-01 23:55:00'
AND    xtime <  LOCALTIMESTAMP;  -- excluding upper bound (optional)

To operate with a count of seconds as interval, just multiply the basic interval quantity:

...
WHERE  xtime >= LOCALTIMESTAMP - interval '1s' * $selectedtimeParm
AND    xtime <  LOCALTIMESTAMP;

In your question you refer to the data type timestamp as "date", "time" and "period". In the title you used the term "time frames", (now updated to "timestamps"). All of these terms are (were) wrong. Freely interchanging them didn't help, either. I had a hard time understand it. That, and the fact that you only tagged (the problem hardly concerns the command line terminal) might explain why nobody answered for days.

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.

Aside: more often than not, the type timestamptz should be preferred over timestamp. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    I found my answer: select * from TABLE where record_time BETWEEN $from_datetime AND NOW(); Thanks – Sharon Jan 19 '12 at 23:47
4

For anyone who is looking for the fix to this. You need to remove timestamp from the where clause and use BETWEEN!

TABLENAME.COL-NAME-FOR-TIMESTAMP BETWEEN '2020-01-29 04:18:00-06' AND CURRENT_TIMESTAMP
user10078199
  • 141
  • 2
  • 8