Literals
A literal is a constant value expression, with special syntax rules for each data type.
Numbers
Number literals can contain number characters as well as a period, underscores
and char e
.
If a number literal contains a dot or character e
, it is treated as floating
point number (or just float), otherwise it is treated as integer number.
Character e
denotes
“scientific notation”,
where the number after e
is the exponent in 10-base.
Underscores are ignored, so they can be placed at arbitrary positions, but it is advised to use them as thousand separators.
Integers can, alternatively, be expressed using hexadecimal, octal or binary
notation using these prefixes respectively: 0x
, 0o
or 0b
.
PRQL
from numbers
select {
small = 1.000_000_1,
big = 5_000_000,
huge = 5e9,
binary = 0x0011,
hex = 0x80,
octal = 0o777,
}
SQL
SELECT
1.0000001 AS small,
5000000 AS big,
5000000000.0 AS huge,
17 AS "binary",
128 AS hex,
511 AS octal
FROM
numbers
Strings
PRQL supports string literals and several other formats of strings. See the Strings page for more information.
Booleans
Boolean values can be expressed with true
or false
keyword.
Null
The null value can be expressed with null
keyword. See also the discussion of
how PRQL handles nulls.
Date and time
Date and time literals are expressed with character @
, followed by a string
that encodes the date & time.
PRQL’s notation is designed to be less verbose than SQL’s
TIMESTAMP '2004-10-19 10:23:54'
and more explicit than SQL’s implicit option
that just uses a string '2004-10-19 10:23:54'
.
Dates
Dates are represented by @{yyyy-mm-dd}
— a @
followed by the date format.
PRQL
from employees
derive age_at_year_end = (@2022-12-31 - dob)
SQL
SELECT
*,
DATE '2022-12-31' - dob AS age_at_year_end
FROM
employees
Times
Times are represented by @{HH:mm:ss.SSS±Z}
with any parts not supplied
defaulting to zero. This includes the timezone, which is represented by
+HH:mm
, -HH:mm
or Z
. This is consistent with the ISO8601 time format.
PRQL
from orders
derive should_have_shipped_today = (order_time < @08:30)
SQL
SELECT
*,
order_time < TIME '08:30' AS should_have_shipped_today
FROM
orders
Timestamps
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.
PRQL
from commits
derive first_prql_commit = @2020-01-01T13:19:55-08:00
derive first_prql_commit_utc = @2020-01-02T21:19:55Z
SQL
SELECT
*,
TIMESTAMP '2020-01-01T13:19:55-0800' AS first_prql_commit,
TIMESTAMP '2020-01-02T21:19:55Z' AS first_prql_commit_utc
FROM
commits
Durations
Durations are represented by {N}{periods}
, such as 2years
or 10minutes
,
without a space.
These aren’t the same as ISO8601, because we evaluated P3Y6M4DT12H30M5S
to
be difficult to understand, but we could support a simplified form if there’s
demand for it. We don’t currently support compound expressions, for example
2years10months
, but most DBs will allow 2years + 10months
. Please raise an
issue if this is inconvenient.
PRQL
from projects
derive first_check_in = start + 10days
SQL
SELECT
*,
"start" + INTERVAL 10 DAY AS first_check_in
FROM
projects
Examples
Here’s a larger list of date and time 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
Currently prqlc does not parse or validate any of the datetime strings and will pass them to the database engine without adjustment. This might be refined in the future to aid in compatibility across databases. We’ll always support the canonical ISO8601 format described above.
Roadmap
Datetimes (as a distinct datatype from the timestamps) are supported by some databases (e.g. MySql, BigQuery). With the addition of type casts, these could be represented by a timestamp cast to a datetime:
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@2022-12-31<datetime>
is forbidden — datetime must specify time@16:54<datetime>
is forbidden — datetime must specify date