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

prql target:sql.duckdb

from invoices
select (invoice_date | date.to_text "%d/%m/%Y")

SQL

SELECT
  strftime(invoice_date, '%d/%m/%Y')
FROM
  invoices

PRQL

prql target:sql.postgres

from invoices
select (invoice_date | date.to_text "%d/%m/%Y")

SQL

SELECT
  TO_CHAR(invoice_date, 'DD/MM/YYYY')
FROM
  invoices

PRQL

prql target:sql.mysql

from invoices
select (invoice_date | date.to_text "%d/%m/%Y")

SQL

SELECT
  DATE_FORMAT(invoice_date, '%d/%m/%Y')
FROM
  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
%-d 8Day 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)
%k 0Same 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