PostgREST datetime operators

This is a quick guide how to deal with datetimes in PostgREST/Supabase.

Timestamp format

Datetimes can be passed to PostgREST conditions normally in ISO 8601 format. These all work:

2023-07-24T15:30:00Z
2023-07-24T15:30
2023-07-24

If you pass in an ISO datetime with a timezone (e.g. 2023-07-24T15:30:00+0300), it will get converted to timezone configured by the server. If server has the relatively standard timezone configuration UTC, this would result in 2023-07-24T12:30:00+00.

When querying, datetimes are also returned in timezone configured by the server.

Special date/time inputs

Postgres specifies a few special inputs that can be used in PostgREST conditions directly. You can find a list of them here or from this quick reference:

Note that for comparison purposes, date inputs (today,tomorrow,yesterday) effectively only work against date columns. For example, trying to query all rows where created_at=today (and created_at is a timestamp) will fail because of the time component.

Query rows where date equals today

This makes some datetime queries especially easy. For instance, querying users that signed up today:

GET /people?select=*&signed_up=eq.today HTTP/1.1

Set column to now upon insert/upsert/update

Importantly, this also works for updates. For instance,

PATCH /people?id=eq.foobar HTTP/1.1
{ "name": "New Name", "updated_at": "now" }

Simple comparisons

gt, gte, lt, lte can be used normally to compare datetimes as you’d expect.

GET /people?select=*&signed_up=lte.2023-07-24 HTTP/1.1

Note that supplying no time automatically expands the time component to midnight, so it’s better to use exclusive end date with the lt operator in that case.

Check if datetime is null or less than given value

You can combine or, is.null, and lt to do a simple check if given timestamp is null or less than given value.

GET /website_uptimes?select=*&or=(last_checked_at.is.null,last_checked_at.lt.2023-08-02) HTTP/1.1

Range comparisons

There is no direct support in PostgREST for Postgres’ BETWEEN comparison predicate, which would allow directly checking whether datetime lies between given input datetimes. However, we can use two datetime comparison operators to simulate it.

GET /tasks?select=*&and=(completed_at.gte.2023-07-01,completed_at.lte.2023-07-31) HTTP/1.1

Date ranges

If you’re storing datetime ranges (daterange, tsrange, tstzrange) in the database, you’ll unlock a few extra operators to use:

ov (overlap), sl (strictly left of), sr (strictly right of), nxr (does not extend to the right of), nxl (does not extend to the left of), adj (is adjacent to)

See https://www.postgresql.org/docs/current/functions-range.html#RANGE-OPERATORS-TABLE for explanations