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 psql (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:
- Ignoring time zones altogether in Rails and PostgreSQL