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).