Literals
- Numbers
  - PRQL
  - SQL
- Strings
  - PRQL
  - SQL
  - PRQL
  - SQL
  - PRQL
  - SQL
  - PRQL
  - SQL
  - PRQL
  - SQL
  - PRQL
  - SQL
- Bool
- Null
- Date and time
  - Dates
    - PRQL
    - SQL
  - Times
    - PRQL
    - SQL
  - Timestamps
    - PRQL
    - SQL
  - Durations
    - PRQL
    - SQL
  - Examples
  - Roadmap

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.

PRQL

1. from numbers
2. select {
3. small = 1.000_000_1,
4. big = 5_000_000,
5. huge = 5e9,
6. }

SQL

1. SELECT
2. 1.0000001 AS small,
3. 5000000 AS big,
4. 5000000000.0 AS huge
5. FROM
6. numbers

Strings

String literals can use either single or double quotes:

PRQL

1. from my_table
2. select x = "hello world"

SQL

1. SELECT
2. 'hello world' AS x
3. FROM
4. my_table

PRQL

1. from my_table
2. select x = 'hello world'

SQL

1. SELECT
2. 'hello world' AS x
3. FROM
4. my_table

To quote a string containing quotes, either use the “other” type of quote, or use 3, 4, 5 or 6 quotes, and close with the same number.

PRQL

1. from my_table
2. select x = '"hello world"'

SQL

1. SELECT
2. '"hello world"' AS x
3. FROM
4. my_table

PRQL

1. from my_table
2. select x = """I said "hello world"!"""

SQL

1. SELECT
2. 'I said "hello world"!' AS x
3. FROM
4. my_table

PRQL

1. from my_table
2. select x = """""I said """hello world"""!"""""

SQL

1. SELECT
2. 'I said """hello world"""!' AS x
3. FROM
4. my_table

Strings can also contain any escape defined by JSON standard.

PRQL

1. from my_table
2. select x = "\t\tline ends here\n \\ "

SQL

1. SELECT
2. '        line ends here
3. \ ' AS x
4. FROM
5. my_table

Bool

Boolean values can be expressed with true or false keyword.

Null

The null value can be expressed with null keyword.

Date and time

Date and time literals are expressed with character @, followed by a string that encodes the date & time.

Note

Comparing to SQL, this notation is less verbose than TIMESTAMP '2004-10-19 10:23:54' and more explicit than SQL’s implicit option of just using a string '2004-10-19 10:23:54'.

Dates

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

PRQL

1. from employees
2. derive age_at_year_end = (@2022-12-31 - dob)

SQL

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

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

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

SQL

1. SELECT
2. *,
3. order_time < TIME '08:30' AS should_have_shipped_today
4. FROM
5. 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

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

SQL

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

Durations

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

Note

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

1. from projects
2. derive first_check_in = start + 10days

SQL

1. SELECT
2. *,
3. start + INTERVAL 10 DAY AS first_check_in
4. FROM
5. projects

Examples

Here’s a fuller 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

Note

Currently prql-compiler 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:

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
@2022-12-31<datetime> is forbidden — datetime must specify time
@16:54<datetime> is forbidden — datetime must specify date

1.1. Literals