Date functions

These are all the functions defined in the date module:

to_text

Converts a date into a text.
Since there are many possible date representations, to_text takes a format parameter that describes thanks to specifiers how the date or timestamp should be structured.

Info

Since all RDBMS have different ways to format dates and times, PRQL requires an explicit dialect to be specified

Info

For now the supported DBs are: Clickhouse, DuckDB, MySQL, MSSQL and Postgres.

PRQL

  1. prql target:sql.duckdb
  3. from invoices
  4. select (invoice_date | date.to_text "%d/%m/%Y")

SQL

  1. SELECT
  2.   strftime(invoice_date, '%d/%m/%Y')
  3. FROM
  4.   invoices

PRQL

  1. prql target:sql.postgres
  3. from invoices
  4. select (invoice_date | date.to_text "%d/%m/%Y")

SQL

  1. SELECT
  2.   TO_CHAR(invoice_date, 'DD/MM/YYYY')
  3. FROM
  4.   invoices

PRQL

  1. prql target:sql.mysql
  3. from invoices
  4. select (invoice_date | date.to_text "%d/%m/%Y")

SQL

  1. SELECT
  2.   DATE_FORMAT(invoice_date, '%d/%m/%Y')
  3. FROM
  4.   invoices

Date & time format specifiers

PRQL specifiers for date and time formatting is a subset of specifiers used by chrono.

Here is the list of the specifiers currently supported:

Spec.ExampleDescription
DATE SPECIFIERS:
%Y2001Year number, zero-padded to 4 digits
%y01Year number, zero-padded to 2 digits
%m07Month number (01–12), zero-padded to 2 digits
%-m7Month number (1-12)
%bJulAbbreviated month name. Always 3 letters.
%BJulyFull month name
%d08Day number (01-31), zero-padded to 2 digits
%-d8Day number (1-31)
%aSunAbbreviated weekday name. Always 3 letters
%ASundayFull weekday name
%D07/08/01Month-day-year format. Same as %m/%d/%y
%x07/08/01Locale’s date representation
%F2001-07-08Year-month-day format (ISO 8601). Same as %Y-%m-%d
TIME SPECIFIERS:
%H00Hour number (00-23)
%k0Same as %H but space-padded. Same as %_H.
%I12Hour number in 12-hour clocks (01–12), zero-padded to 2 digits.
%pAMAM or PM in 12-hour clocks.
%M34Minute number (00-59), zero-padded to 2 digits.
%S60Second number (00-59), zero-padded to 2 digits.
%f264900Number of microseconds1 since last whole second
%R00:34Hour-minute format. Same as %H:%M.
%T00:34:60Hour-minute-second format. Same as %H:%M:%S.
%X00:34:60Locale’s time representation (e.g., 23:13:48).
%r12:34:60 AMLocale’s 12 hour clock time. (e.g., 11:11:04 PM)
DATE & TIME SPECIFIERS:
%+2001-07-08T00:34:60.026490ZISO 8601 / RFC 3339 date & time format.
SPECIAL SPECIFIERS:
%tLiteral tab (\t).
%nLiteral newline (\n).
%%Literal percent sign.

1

This is different from chrono, for which %f represents nanoseconds