Dates and times

ReflexDB stores and returns all date and time values in UTC. Timezone offsets from the source database are applied at ingest time — the resulting UTC value is what you query and receive.

Serialisation formats

SQL type	JSON format	Example
DATE	'YYYY-MM-DD'	'2024-06-15'
DATETIME, TIMESTAMP, TIMESTAMPTZ	'YYYY-MM-DDTHH:MM:SSZ'	'2024-06-15T12:30:00Z'
TIME, TIMETZ	Integer (total seconds)	45000

---

Filtering

When filtering on date or datetime columns, any of these formats are accepted:

events(created_at > '2024-06-15T12:30:00Z') { id title }
events(created_at > '2024-06-15T12:30:00') { id title }
events(created_at > '2024-06-15 12:30:00') { id title }

All three are equivalent. Date columns use 'YYYY-MM-DD':

events(date_col = '2024-06-15') { id title }

Time/duration columns are integer seconds:

events(duration > 3600) { id title }

Tip

BETWEEN works with dates and datetimes:

events(created_at BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59') { id }

---

Source database behaviour

MySQL / MariaDB

DATETIME and TIMESTAMP values do not carry timezone information in the wire protocol. ReflexDB stores the values as received.

Caution

If your MySQL server uses a non-UTC timezone, replicated values will reflect the server’s local time interpreted as UTC. Set default-time-zone = '+00:00' in your MySQL configuration to avoid offset issues.

PostgreSQL

TIMESTAMP WITH TIME ZONE (timestamptz) values include a timezone offset. ReflexDB applies the offset and stores the result in UTC.

TIMESTAMP WITHOUT TIME ZONE values are stored as-is (assumed UTC).

TIME WITH TIME ZONE (timetz) offsets are applied to produce a UTC time-of-day value. The original timezone is not preserved.

SQL Server

DATETIMEOFFSET values include a timezone offset which is applied to produce UTC. DATETIME and DATETIME2 values are stored as-is (assumed UTC).