How to request relative date (ie, CURRENT_DATE - 30)

[getglad]

I have a table with a column where I would like to get the results that are older than the past 30 days. I've been looking at the timestamp documentation but I can't understand how to do this. I am beginning to assume my best solution is going to be to create a view, but I want to be sure I am not missing something.

https://postgrest.org/en/stable/how-tos/working-with-postgresql-data-types.html?highlight=date#timestamps

So in pg I can do something like:

postgres> select creation_date from table_name where creation_date < CURRENT_DATE - 30 limit 10
+------------------------+
| creation_date          |
|------------------------|
| 2021-08-31 01:11:48+00 |
| 2021-06-21 01:10:16+00 |
| 2021-01-14 06:19:34+00 |
| 2023-02-09 19:06:27+00 |
| 2021-11-07 01:10:05+00 |
| 2022-11-12 12:39:41+00 |
| 2022-04-18 01:26:30+00 |
| 2023-01-23 01:33:57+00 |
| 2021-07-28 00:37:51+00 |
| 2021-09-15 01:17:07+00 |
+------------------------+

In pgREST if I query with table_name?limit=10&creation_date=lt.now I can get a result.

But with table_name?limit=10&creation_date=lt.current_date, I get "message": "invalid input syntax for type timestamp with time zone: \"current_date\""

Not sure if I am doing something wrong here, or perhaps missing a syntax quirk? Much less how to replicate the CURRENT_DATE - 30.

Happy to provide more context bu not sure what is helpful.

[wolfgangwalther]

PostgreSQL has two different "now"s:

• A function now(), which returns the same as CURRENT_TIMESTAMP. This is the similar to CURRENT_DATE. You seem to assume you are calling this with lt.now, but you are not.
• A special value which can be read as input for timestamptz or date types. This is what you are using with lt.now.

There is no such special input value for timestamptz or date for "current_date". That's the error message you are seeing.

Also see the PG docs in 8.5.1.4. Special Values:

The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. [...] Note that these are SQL functions and are not recognized in data input strings.

---

As for a solution, imho you have several ways to do this:

• Create a view with the hardcoded 30 days, if that is a constant that will never change.
• Create a plain SQL, STABLE function as RPC with an argument for the 30 days, if you need to be flexible.
• Just calculate "now - 30 days" on the client and do lt.<exact_date>, if you like to keep it simple.