Dates & times

Note

Currently PRQL passes strings which can be compiled straight through to the database, and so many compatible formats string may work, but we may refine this in the future to aid in compatibility across databases. We’ll always support the canonical format described below.

Dates are represented by @{yyyy-mm-dd} — a @ followed by the date format.

PRQL

SQL

  1.   *,
  2.   DATE '2022-12-31' - dob AS age_at_year_end
  3. FROM
  4.   employees

Times

PRQL

  1. from orders
  2. derive should_have_shipped_today = (order_time < @08:30)

SQL

Timestamps are represented by @{yyyy-mm-ddTHH:mm:ss.SSS±Z} / @{date}T{time}, with any time parts not supplied being rounded to zero, including the timezone, which is represented by +HH:mm, -HH:mm or Z (: is optional). This is followed by the ISO8601 datetime format, which uses T to separate date & time.

  1. from commits
  2. derive first_prql_commit = @2020-01-01T13:19:55-08:00

  1. SELECT
  2.   *,
  3.   TIMESTAMP '2020-01-01T13:19:55-08:00' AS first_prql_commit
  4. FROM
  5.   commits

Intervals are represented by {N}{periods}, such as 2years or 10minutes, without a space.

Note

  1. SELECT
  2.   *,
  3. FROM
  4.   projects

Here’s a fuller list of examples:

  • @20221231 is invalid — it must contain full punctuation (- and ),
  • @2022-12-31 is a date
  • @2022-12 or @2022 are invalid — SQL can’t express a month, only a date
  • @16:54:32.123456 is a time
  • @16:54:32, @16:54, @16 are all allowed, expressing @16:54:32.000000, @16:54:00.000000, @16:00:00.000000 respectively
  • @2022-12-31T16:54:32.123456 is a timestamp without timezone
  • @2022-12-31T16:54:32.123456Z is a timestamp in UTC
  • @2022-12-31T16:54+02 is timestamp in UTC+2
  • @2022-12-31T16:54+02:00 and @2022-12-31T16:54+02 are datetimes in UTC+2
  • @16:54+02 is invalid — time is always local, so it cannot have a timezone
  • @2022-12-31+02 is invalid — date is always local, so it cannot have a timezone

Roadmap

Datetimes are supported by some databases (e.g. MySql, BigQuery) in addition to timestamps. When we have type annotations, these will be represented by a timestamp annotated as a datetime:

  1. derive pi_day = @2017-03-14T15:09:26.535898<datetime>

These are some examples we can then add:

  • @2022-12-31T16:54<datetime> is datetime without timezone
  • @16:54<datetime> is forbidden — datetime must specify date