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.
[!NOTE] Since all RDBMS have different ways to format dates and times, PRQL requires an explicit dialect to be specified
[!NOTE] 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. | Example | Description |
|---|---|---|
| DATE SPECIFIERS: | ||
%Y | 2001 | Year number, zero-padded to 4 digits |
%y | 01 | Year number, zero-padded to 2 digits |
%m | 07 | Month number (01–12), zero-padded to 2 digits |
%-m | 7 | Month number (1-12) |
%b | Jul | Abbreviated month name. Always 3 letters. |
%B | July | Full month name |
%d | 08 | Day number (01-31), zero-padded to 2 digits |
%-d | 8 | Day number (1-31) |
%a | Sun | Abbreviated weekday name. Always 3 letters |
%A | Sunday | Full weekday name |
%D | 07/08/01 | Month-day-year format. Same as %m/%d/%y |
%x | 07/08/01 | Locale’s date representation |
%F | 2001-07-08 | Year-month-day format (ISO 8601). Same as %Y-%m-%d |
| TIME SPECIFIERS: | ||
%H | 00 | Hour number (00-23) |
%k | 0 | Same as %H but space-padded. Same as %_H. |
%I | 12 | Hour number in 12-hour clocks (01–12), zero-padded to 2 digits. |
%p | AM | AM or PM in 12-hour clocks. |
%M | 34 | Minute number (00-59), zero-padded to 2 digits. |
%S | 60 | Second number (00-59), zero-padded to 2 digits. |
%f | 264900 | Number of microseconds1 since last whole second |
%R | 00:34 | Hour-minute format. Same as %H:%M. |
%T | 00:34:60 | Hour-minute-second format. Same as %H:%M:%S. |
%X | 00:34:60 | Locale’s time representation (e.g., 23:13:48). |
%r | 12:34:60 AM | Locale’s 12 hour clock time. (e.g., 11:11:04 PM) |
| DATE & TIME SPECIFIERS: | ||
%+ | 2001-07-08T00:34:60.026490Z | ISO 8601 / RFC 3339 date & time format. |
| SPECIAL SPECIFIERS: | ||
%t | Literal tab (\t). | |
%n | Literal newline (\n). | |
%% | Literal percent sign. |
-
This is different from chrono, for which
%frepresents nanoseconds ↩