Repository: prql/prql
Pull request #377 (2022-04-17 22:51:08)

Literal dates & times
These aren't yet implemented. They would be implemented in the same way
that Intervals are implemented.

Would close https://github.com/prql/prql/issues/373


max-sixty: Another option for this would be `d"2022-12-31"` rather than `d2022-12-31` — a bit less slick, but it removes any ambiguity — e.g. if there were a column actually called `d2022-12-31` in a DB (?!)
max-sixty: I realized that `T10:30` can't easily be parsed as a time rather than a named expr `T10`, `:`, `30`.

So some options:
- `T"10:30"` (or `t"10:30"`)
- `T10.30`, but then no longer consistent with standards
- Adding code to the named expr code to exclude `T\d{2}` prefixes. Which is OK in a practical sense, but the workaround is a symptom of an underlying issue, and makes the parser code messy and less modular
aljazerzen: Having `d2022-12-31` and `t10:40` is really convenient, but I would say it's not worth it if it adds a new parsing pattern.

What about having `@` for all datetime types: `@2022-12-31T10:40`, `@2022-12-31`, `@10:40`, which compile to datetime, date and time, respectively?

It introduces a new parsing pattern, but it covers all date-time formats at once, which is a bit better.
max-sixty: The issue with `@` is how to differentiate between similar but different types e.g. `datetime` and `timestamp`. Though possibly there are workarounds...
max-sixty: Ready to go!

Patch:
diff --git a/Cargo.lock b/Cargo.lock
index 6adecda3..edfe6fc0 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -306,6 +306,12 @@ dependencies = [
  "wasm-bindgen",
 ]
 
+[[package]]
+name = "const-sha1"
+version = "0.2.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "fb58b6451e8c2a812ad979ed1d83378caa5e927eef2622017a45f251457c2c9d"
+
 [[package]]
 name = "convert_case"
 version = "0.4.0"
@@ -551,6 +557,16 @@ version = "1.0.7"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "3f9eec918d3f24069decb9af1554cad7c880e2da24a9afd88aca000531ab82c1"
 
+[[package]]
+name = "form_urlencoded"
+version = "1.0.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "5fc25a87fa4fd2094bffb06925852034d90a17f0d1e05197d4956d3555752191"
+dependencies = [
+ "matches",
+ "percent-encoding",
+]
+
 [[package]]
 name = "generic-array"
 version = "0.12.4"
@@ -755,6 +771,17 @@ dependencies = [
  "quick-error 1.2.3",
 ]
 
+[[package]]
+name = "idna"
+version = "0.2.3"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "418a0a6fab821475f634efe3ccc45c013f742efe03d853e8d3355d5cb850ecf8"
+dependencies = [
+ "matches",
+ "unicode-bidi",
+ "unicode-normalization",
+]
+
 [[package]]
 name = "indenter"
 version = "0.3.3"
@@ -865,19 +892,34 @@ dependencies = [
 
 [[package]]
 name = "log"
-version = "0.4.16"
+version = "0.4.17"
 source = "registry+https://github.com/rust-lang/crates.io-index"
-checksum = "6389c490849ff5bc16be905ae24bc913a9c8892e19b2341dbc175e14c341c2b8"
+checksum = "abb12e687cfb44aa40f41fc3978ef76448f9b6038cad6aef4259d3c095a2382e"
 dependencies = [
  "cfg-if 1.0.0",
 ]
 
+[[package]]
+name = "malloc_buf"
+version = "0.0.6"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "62bb907fe88d54d8d9ce32a3cceab4218ed2f6b7d35617cafe9adf84e43919cb"
+dependencies = [
+ "libc",
+]
+
 [[package]]
 name = "maplit"
 version = "1.0.2"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "3e2e65a1a2e43cfcb47a895c4c8b10d1f4a61097f9f254f183aee60cad9c651d"
 
+[[package]]
+name = "matches"
+version = "0.1.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "a3e378b66a060d48947b590737b30a1be76706c8dd7b8ba0f2fe3989c68a853f"
+
 [[package]]
 name = "mdbook"
 version = "0.4.18"
@@ -1025,6 +1067,15 @@ dependencies = [
  "libc",
 ]
 
+[[package]]
+name = "objc"
+version = "0.2.7"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "915b1b472bc21c53464d6c8461c9d3af805ba1ef837e1cac254428f4a77177b1"
+dependencies = [
+ "malloc_buf",
+]
+
 [[package]]
 name = "object"
 version = "0.28.3"
@@ -1127,6 +1178,12 @@ version = "1.0.7"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "0c520e05135d6e763148b6426a837e239041653ba7becd2e538c076c738025fc"
 
+[[package]]
+name = "percent-encoding"
+version = "2.1.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "d4fd5641d01c8f18a23da7b6fe29298ff4b55afcccdf78973b24cf3175fee32e"
+
 [[package]]
 name = "pest"
 version = "2.1.3"
@@ -1351,10 +1408,12 @@ dependencies = [
  "globset",
  "insta",
  "itertools",
+ "log",
  "prql-compiler",
  "pulldown-cmark",
  "pulldown-cmark-to-cmark",
  "similar",
+ "trash",
  "walkdir",
 ]
 
@@ -1903,6 +1962,21 @@ dependencies = [
  "serde_json",
 ]
 
+[[package]]
+name = "tinyvec"
+version = "1.6.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "87cc5ceb3875bb20c2890005a4e226a4651264a5c75edb2421b52861a0a0cb50"
+dependencies = [
+ "tinyvec_macros",
+]
+
+[[package]]
+name = "tinyvec_macros"
+version = "0.1.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "cda74da7e1a664f795bb1f8a87ec406fb89a02522cf6e50620d016add6dbbf5c"
+
 [[package]]
 name = "toml"
 version = "0.5.9"
@@ -1960,6 +2034,22 @@ dependencies = [
  "tracing-core",
 ]
 
+[[package]]
+name = "trash"
+version = "2.0.4"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "9a2ed4369f59214865022230fb397ad71353101fe87bfef0f0cf887c43eaa094"
+dependencies = [
+ "chrono",
+ "libc",
+ "log",
+ "objc",
+ "once_cell",
+ "scopeguard",
+ "url",
+ "windows",
+]
+
 [[package]]
 name = "typenum"
 version = "1.15.0"
@@ -1981,6 +2071,21 @@ dependencies = [
  "version_check",
 ]
 
+[[package]]
+name = "unicode-bidi"
+version = "0.3.8"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "099b7128301d285f79ddd55b9a83d5e6b9e97c92e0ea0daebee7263e932de992"
+
+[[package]]
+name = "unicode-normalization"
+version = "0.1.19"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "d54590932941a9e9266f0832deed84ebe1bf2e4c9e4a3554d393d18f5e854bf9"
+dependencies = [
+ "tinyvec",
+]
+
 [[package]]
 name = "unicode-width"
 version = "0.1.9"
@@ -2005,6 +2110,18 @@ version = "0.1.8"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "514672a55d7380da379785a4d70ca8386c8883ff7eaae877be4d2081cebe73d8"
 
+[[package]]
+name = "url"
+version = "2.2.2"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "a507c383b2d33b5fc35d1861e77e6b383d158b2da5e14fe51b83dfedf6fd578c"
+dependencies = [
+ "form_urlencoded",
+ "idna",
+ "matches",
+ "percent-encoding",
+]
+
 [[package]]
 name = "valuable"
 version = "0.1.0"
@@ -2190,6 +2307,17 @@ version = "0.4.0"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "712e227841d057c1ee1cd2fb22fa7e5a5461ae8e48fa2ca79ec42cfc1931183f"
 
+[[package]]
+name = "windows"
+version = "0.9.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "361f3533a83ee1a28c9be59683f40043db02dbedf6479ce8795657386195c97f"
+dependencies = [
+ "const-sha1",
+ "windows_gen",
+ "windows_macros",
+]
+
 [[package]]
 name = "windows-sys"
 version = "0.34.0"
@@ -2209,6 +2337,15 @@ version = "0.34.0"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "17cffbe740121affb56fad0fc0e421804adf0ae00891205213b5cecd30db881d"
 
+[[package]]
+name = "windows_gen"
+version = "0.9.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "54154dbc515d58723f6b6053c12f1065da7389f733660581b2391bd1af480452"
+dependencies = [
+ "syn",
+]
+
 [[package]]
 name = "windows_i686_gnu"
 version = "0.34.0"
@@ -2221,6 +2358,16 @@ version = "0.34.0"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "9cd9d32ba70453522332c14d38814bceeb747d80b3958676007acadd7e166956"
 
+[[package]]
+name = "windows_macros"
+version = "0.9.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "0f7794c652845dc466cb8dc1b86c08345707c8144bc53e9086430047c7d33b76"
+dependencies = [
+ "syn",
+ "windows_gen",
+]
+
 [[package]]
 name = "windows_x86_64_gnu"
 version = "0.34.0"
diff --git a/prql-compiler/src/ast/ast_fold.rs b/prql-compiler/src/ast/ast_fold.rs
index 4c5795e7..c63176b1 100644
--- a/prql-compiler/src/ast/ast_fold.rs
+++ b/prql-compiler/src/ast/ast_fold.rs
@@ -113,15 +113,20 @@ pub fn fold_item<T: ?Sized + AstFold>(fold: &mut T, item: Item) -> Result<Item>
         Item::FuncDef(func) => Item::FuncDef(fold.fold_func_def(func)?),
         Item::FuncCall(func_call) => Item::FuncCall(fold.fold_func_call(func_call)?),
         Item::Table(table) => Item::Table(fold.fold_table(table)?),
-        // None of these capture variables, so we don't need to replace
-        // them.
-        Item::String(_) | Item::Raw(_) | Item::Interval(_) => item,
         Item::Windowed(window) => Item::Windowed(Windowed {
             expr: Box::new(fold.fold_node(*window.expr)?),
             group: fold.fold_nodes(window.group)?,
             sort: fold.fold_column_sorts(window.sort)?,
         }),
         Item::Type(t) => Item::Type(fold.fold_type(t)?),
+        // None of these capture variables, so we don't need to replace
+        // them.
+        Item::String(_)
+        | Item::Raw(_)
+        | Item::Interval(_)
+        | Item::Date(_)
+        | Item::Time(_)
+        | Item::Timestamp(_) => item,
     })
 }
 
diff --git a/prql-compiler/src/ast/item.rs b/prql-compiler/src/ast/item.rs
index 4767a7bb..07ca6197 100644
--- a/prql-compiler/src/ast/item.rs
+++ b/prql-compiler/src/ast/item.rs
@@ -30,6 +30,9 @@ pub enum Item {
     SString(Vec<InterpolateItem>),
     FString(Vec<InterpolateItem>),
     Interval(Interval),
+    Date(String),
+    Time(String),
+    Timestamp(String),
     Windowed(Windowed),
 }
 
@@ -249,6 +252,9 @@ impl Display for Item {
             Item::Interval(i) => {
                 write!(f, "{}{}", i.n, i.unit)?;
             }
+            Item::Date(inner) | Item::Time(inner) | Item::Timestamp(inner) => {
+                write!(f, "@{inner}")?;
+            }
             Item::Windowed(w) => {
                 write!(f, "{:?}", w.expr)?;
             }
diff --git a/prql-compiler/src/parser.rs b/prql-compiler/src/parser.rs
index b985a8f2..3aa94232 100644
--- a/prql-compiler/src/parser.rs
+++ b/prql-compiler/src/parser.rs
@@ -225,6 +225,18 @@ fn ast_of_parse_tree(pairs: Pairs<Rule>) -> Result<Vec<Node>> {
                         unit: unit.as_str().to_owned(),
                     })
                 }
+                Rule::date => {
+                    let parsed = ast_of_parse_tree(pair.into_inner());
+                    Item::Date(parsed?.into_only()?.item.into_raw()?)
+                }
+                Rule::time => {
+                    let parsed = ast_of_parse_tree(pair.into_inner());
+                    Item::Time(parsed?.into_only()?.item.into_raw()?)
+                }
+                Rule::timestamp => {
+                    let parsed = ast_of_parse_tree(pair.into_inner());
+                    Item::Timestamp(parsed?.into_only()?.item.into_raw()?)
+                }
                 Rule::type_def => {
                     let mut parts: Vec<_> = pair.into_inner().into_iter().collect();
                     let name = parts.remove(0).as_str().to_string();
@@ -238,16 +250,18 @@ fn ast_of_parse_tree(pairs: Pairs<Rule>) -> Result<Vec<Node>> {
 
                     Item::Type(Type { name, param })
                 }
-
-                Rule::operator_unary
+                Rule::number
+                | Rule::interval_kind
+                | Rule::date_inner
+                | Rule::time_inner
+                | Rule::timestamp_inner
+                | Rule::operator_unary
                 | Rule::operator_mul
                 | Rule::operator_add
                 | Rule::operator_compare
-                | Rule::operator_logical
-                | Rule::interval_kind
-                | Rule::number => Item::Raw(pair.as_str().to_owned()),
+                | Rule::operator_logical => Item::Raw(pair.as_str().to_owned()),
 
-                _ => unreachable!(),
+                _ => unreachable!("{pair}"),
             };
 
             let mut node = Node::from(item);
@@ -1383,7 +1397,7 @@ select [
     }
 
     #[test]
-    fn test_interval() {
+    fn test_dates() -> Result<()> {
         assert_yaml_snapshot!(parse("
         from employees
         derive [age_plus_two_years = (age + 2years)]
@@ -1415,6 +1429,42 @@ select [
                                       unit: years
                     named_args: {}
         "###);
+
+        assert_yaml_snapshot!(parse("
+        derive [
+            date = @2011-02-01,
+            timestamp = @2011-02-01T10:00,
+            time = @14:00,
+            # datetime = @2011-02-01T10:00<datetime>,
+        ]
+        ").unwrap(), @r###"
+        ---
+        version: ~
+        dialect: Generic
+        nodes:
+          - Pipeline:
+              value: ~
+              functions:
+                - FuncCall:
+                    name: derive
+                    args:
+                      - List:
+                          - Assign:
+                              name: date
+                              expr:
+                                Date: 2011-02-01
+                          - Assign:
+                              name: timestamp
+                              expr:
+                                Timestamp: "2011-02-01T10:00"
+                          - Assign:
+                              name: time
+                              expr:
+                                Time: "14:00"
+                    named_args: {}
+        "###);
+
+        Ok(())
     }
 
     #[test]
diff --git a/prql-compiler/src/prql.pest b/prql-compiler/src/prql.pest
index 0839cc70..91312163 100644
--- a/prql-compiler/src/prql.pest
+++ b/prql-compiler/src/prql.pest
@@ -72,7 +72,7 @@ expr_add = !{ expr_mul ~ (operator_add ~ expr_mul)* }
 expr_mul = !{ term ~ (operator_mul ~ term)* }
 
 term = _{ ( s_string | f_string | ident | nested_expr | nested_pipeline | list | range | literal ) }
-literal = _{ interval | number | string}
+literal = _{ interval | number | string | timestamp | date | time }
 list = { "[" ~ (NEWLINE* ~ (assign_call | expr_call) ~ ("," ~ NEWLINE* ~ (assign_call | expr_call) )* ~ ","?)? ~ NEWLINE* ~ "]" }
 nested_expr = _{ "(" ~ expr_call ~ ")" }
 nested_pipeline = { "(" ~ expr_call? ~ pipe ~ pipeline ~ NEWLINE* ~ ")" }
@@ -97,7 +97,7 @@ multi_quote = _{ "\""{3,} | "'"{3,} }
 // PEEK refers to the opening quote; either `"` or `'`.
 string_inner = { ( !( PEEK ) ~ ANY )+ }
 // Either > 3 quotes, or just one. Currently both of those can be multiline.
-string= ${ ( PUSH(multi_quote) | PUSH(quote) ) ~ string_inner ~ POP }
+string = ${ ( PUSH(multi_quote) | PUSH(quote) ) ~ string_inner ~ POP }
 
 number = ${ ( ASCII_DIGIT )+ ~ ("." ~ ( ASCII_DIGIT )+)? }
 
@@ -121,3 +121,12 @@ interpolate_string_inner = { ( !( PEEK | "{" ) ~ ANY )+ }
 
 interval_kind = { "microseconds" | "milliseconds" | "seconds" | "minutes" | "hours" | "days" | "weeks" | "months" | "years" }
 interval = ${ number ~ interval_kind }
+
+date = ${ "@" ~ date_inner }
+time = ${ "@" ~ time_inner }
+timestamp = ${ "@" ~ timestamp_inner }
+// We use the `inner` types as containing the data that we want to retain in the AST.
+date_inner = ${ ASCII_DIGIT{4} ~ "-" ~ ASCII_DIGIT{2} ~ "-" ~ ASCII_DIGIT{2} }
+// Times are liberally defined atm, we could make this more robust.
+time_inner = ${ ASCII_DIGIT{2} ~ ( ASCII_DIGIT | ":" | "." | "+" | "-" | "Z" )* }
+timestamp_inner = ${ date_inner ~ "T" ~ time_inner }
diff --git a/prql-compiler/src/sql/translator.rs b/prql-compiler/src/sql/translator.rs
index 2394abbd..8780ea1a 100644
--- a/prql-compiler/src/sql/translator.rs
+++ b/prql-compiler/src/sql/translator.rs
@@ -549,6 +549,19 @@ impl TryFrom<Item> for Expr {
 
                 Item::Ident(format!("{expr} OVER ({window})")).try_into()?
             }
+            Item::Date(literal) => Expr::TypedString {
+                data_type: sql_ast::DataType::Date,
+                value: literal,
+            },
+            Item::Time(literal) => Expr::TypedString {
+                data_type: sql_ast::DataType::Time,
+                value: literal,
+            },
+            Item::Timestamp(literal) => Expr::TypedString {
+                data_type: sql_ast::DataType::Timestamp,
+                value: literal,
+            },
+
             _ => bail!("Can't convert to Expr; {item:?}"),
         })
     }
@@ -1258,6 +1271,30 @@ take 20
         Ok(())
     }
 
+    #[test]
+    fn test_dates() -> Result<()> {
+        let query: Query = parse(
+            r###"
+        derive [
+            date = @2011-02-01,
+            timestamp = @2011-02-01T10:00,
+            time = @14:00,
+            # datetime = @2011-02-01T10:00<datetime>,
+        ]
+
+        "###,
+        )?;
+
+        assert_display_snapshot!((resolve_and_translate(query)?), @r###"
+        SELECT
+          DATE '2011-02-01' AS date,
+          TIMESTAMP '2011-02-01T10:00' AS timestamp,
+          TIME '14:00' AS time
+        "###);
+
+        Ok(())
+    }
+
     #[test]
     fn test_window_functions() -> Result<()> {
         let query: Query = parse(
diff --git a/reference/Cargo.toml b/reference/Cargo.toml
index b435aaf5..083bb993 100644
--- a/reference/Cargo.toml
+++ b/reference/Cargo.toml
@@ -14,7 +14,11 @@ prql-compiler = {path = "../prql-compiler"}
 [dev-dependencies]
 globset = "0.4.8"
 insta = {version = "^1.13", features = ["colors", "glob"]}
+log = "0.4.17"
 pulldown-cmark = "0.9.1"
 pulldown-cmark-to-cmark = "10"
 similar = "^2.1"
 walkdir = "2.3.2"
+
+[target.'cfg(not(target_family="wasm"))'.dev-dependencies]
+trash = "2.0.4"
diff --git a/reference/src/SUMMARY.md b/reference/src/SUMMARY.md
index 8c0cf4c6..ea90c348 100644
--- a/reference/src/SUMMARY.md
+++ b/reference/src/SUMMARY.md
@@ -9,7 +9,7 @@
   - [S-Strings](./language-features/s-strings.md)
   - [Ranges](./language-features/ranges.md)
     <!-- - - Dates — `"2021-01-01"` -> `@2021-01-01`? And `DATE_TRUNC(foo_date, YEAR)` -> `foo_date.year`? Or -> `foo_date | as year`? Or `foo_date | to year`? -->
-  - [Dates](./language-features/dates.md)
+  - [Dates & Times](./language-features/dates_and_times.md)
     <!--   - Regex — `REGEX_MATCH(foo, "\\w{3}")` -> `foo ~ r"\w{3}"`? Or -> `regex foo r"\w{3}"`? -->
   - [Regex]()
 
diff --git a/reference/src/language-features/dates.md b/reference/src/language-features/dates.md
deleted file mode 100644
index 493676f7..00000000
--- a/reference/src/language-features/dates.md
+++ /dev/null
@@ -1,17 +0,0 @@
-# Dates
-
-## Intervals
-
-Intervals 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
-```
diff --git a/reference/src/language-features/dates_and_times.md b/reference/src/language-features/dates_and_times.md
new file mode 100644
index 00000000..978a51ef
--- /dev/null
+++ b/reference/src/language-features/dates_and_times.md
@@ -0,0 +1,94 @@
+# Dates & Times
+
+PRQL uses a pattern of prefacing strings with their type to represent dates.
+This is less verbose than SQL's approach of `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'`.
+
+> Currently PRQL passes strings straight through to the database, and so any
+compatible format string will work, but we may refine this in the future to aid
+in compatibility across databases. We'll always support the
+[ISO8601](https://en.wikipedia.org/wiki/ISO_8601) format.
+
+## Dates
+
+Dates are represented by `@{yyyy-mm-dd}` — a `D` followed by the
+date format.
+
+```prql
+from employees
+derive age_at_year_end = (@2022-12-31 - dob)
+```
+
+## Times
+
+Times are represented by `@{HH:mm:ss.SSS±Z}` with any parts not supplied being
+rounded to zero, including 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)
+```
+
+## 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`. This
+is `@` followed by the ISO8601 time format, which uses `T` to separate date &
+time.
+
+```prql
+derive first_prql_commit = @2020-01-01T13:19:55-0800
+```
+
+## Intervals
+
+Intervals 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
+```
+
+## Examples
+
+Here's a fuller list of examples:
+
+- `@20221231` is forbidden — it must contain full punctuation (`-` and `:`),
+- `@2022-12-31` is a date
+- `@2022-12` or `@2022` are forbidden — 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 forbidden — time is always local, so it cannot have a timezone
+- `@2022-12-31+02` is forbidden — date is always local, so it cannot have a timezone
+
+## TODOs
+
+### Datetimes
+
+Datetimes are supported by some databases (e.g. MySql, BigQuery) in addition to
+timestamps. When we have type annotations, these will be represented by a
+timestamp annotated as a datetime:
+
+```prql_no_test
+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


Test patch:
diff --git a/reference/tests/examples/language-features/dates_and_times-0.prql b/reference/tests/examples/language-features/dates_and_times-0.prql
new file mode 100644
index 00000000..9bc1efe5
--- /dev/null
+++ b/reference/tests/examples/language-features/dates_and_times-0.prql
@@ -0,0 +1,2 @@
+from employees
+derive age_at_year_end = (@2022-12-31 - dob)
diff --git a/reference/tests/examples/language-features/dates_and_times-1.prql b/reference/tests/examples/language-features/dates_and_times-1.prql
new file mode 100644
index 00000000..83107818
--- /dev/null
+++ b/reference/tests/examples/language-features/dates_and_times-1.prql
@@ -0,0 +1,2 @@
+from orders
+derive should_have_shipped_today = (order_time < @08:30)
diff --git a/reference/tests/examples/language-features/dates_and_times-2.prql b/reference/tests/examples/language-features/dates_and_times-2.prql
new file mode 100644
index 00000000..6e6487a2
--- /dev/null
+++ b/reference/tests/examples/language-features/dates_and_times-2.prql
@@ -0,0 +1,1 @@
+derive first_prql_commit = @2020-01-01T13:19:55-0800
diff --git a/reference/tests/examples/language-features/dates-0.prql b/reference/tests/examples/language-features/dates_and_times-3.prql
similarity index 100%
rename from reference/tests/examples/language-features/dates-0.prql
rename to reference/tests/examples/language-features/dates_and_times-3.prql
diff --git a/reference/tests/snapshot.rs b/reference/tests/snapshot.rs
index 06583004..333036fa 100644
--- a/reference/tests/snapshot.rs
+++ b/reference/tests/snapshot.rs
@@ -1,3 +1,4 @@
+#![cfg(not(target_family = "wasm"))]
 /// This test:
 /// - Extracts PRQL code blocks into the `examples` path.
 /// - Converts them to SQL using insta, raising an error if there's a diff.
@@ -17,6 +18,7 @@
 use anyhow::{bail, Result};
 use globset::Glob;
 use insta::{assert_snapshot, glob};
+use log::warn;
 use prql_compiler::*;
 use pulldown_cmark::{CodeBlockKind, Event, Parser, Tag};
 use std::fs;
@@ -50,6 +52,18 @@ fn run_examples() -> Result<()> {
 // it only for windows?)
 #[allow(dead_code)]
 fn write_reference_examples() -> Result<()> {
+    // Remove old examples, since we're going to rewrite them, and we don't want
+    // old files which wouldn't be rewritten from hanging around.
+    // We use `trash`, since we don't want to be removing files with test code
+    // in case there's a bug.
+
+    let examples_path = Path::new("tests/examples");
+    if examples_path.exists() {
+        trash::delete(Path::new("tests/examples")).unwrap_or_else(|e| {
+            warn!("Failed to delete old examples: {}", e);
+        });
+    }
+
     let glob = Glob::new("**/*.md")?.compile_matcher();
 
     WalkDir::new(Path::new("./src/"))
diff --git a/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-0.prql.snap b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-0.prql.snap
new file mode 100644
index 00000000..5fbb282c
--- /dev/null
+++ b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-0.prql.snap
@@ -0,0 +1,10 @@
+---
+source: reference/tests/snapshot.rs
+expression: sql
+input_file: reference/tests/examples/language-features/dates_and_times-0.prql
+---
+SELECT
+  employees.*,
+  DATE '2022-12-31' - dob AS age_at_year_end
+FROM
+  employees
diff --git a/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-1.prql.snap b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-1.prql.snap
new file mode 100644
index 00000000..f1b48b07
--- /dev/null
+++ b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-1.prql.snap
@@ -0,0 +1,10 @@
+---
+source: reference/tests/snapshot.rs
+expression: sql
+input_file: reference/tests/examples/language-features/dates_and_times-1.prql
+---
+SELECT
+  orders.*,
+  order_time < TIME '08:30' AS should_have_shipped_today
+FROM
+  orders
diff --git a/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-2.prql.snap b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-2.prql.snap
new file mode 100644
index 00000000..7fe9c765
--- /dev/null
+++ b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-2.prql.snap
@@ -0,0 +1,7 @@
+---
+source: reference/tests/snapshot.rs
+expression: sql
+input_file: reference/tests/examples/language-features/dates_and_times-2.prql
+---
+SELECT
+  TIMESTAMP '2020-01-01T13:19:55-0800' AS first_prql_commit
diff --git a/reference/tests/snapshots/snapshot__run_reference_examples@dates-0.prql.snap b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-3.prql.snap
similarity index 64%
rename from reference/tests/snapshots/snapshot__run_reference_examples@dates-0.prql.snap
rename to reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-3.prql.snap
index 518f2a7d..eece9deb 100644
--- a/reference/tests/snapshots/snapshot__run_reference_examples@dates-0.prql.snap
+++ b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-3.prql.snap
@@ -1,7 +1,7 @@
 ---
 source: reference/tests/snapshot.rs
 expression: sql
-input_file: reference/tests/examples/language-features/dates-0.prql
+input_file: reference/tests/examples/language-features/dates_and_times-3.prql
 ---
 SELECT
   projects.*,
diff --git a/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-4.prql.snap b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-4.prql.snap
new file mode 100644
index 00000000..0219f3fd
--- /dev/null
+++ b/reference/tests/snapshots/snapshot__run_reference_examples@dates_and_times-4.prql.snap
@@ -0,0 +1,10 @@
+---
+source: reference/tests/snapshot.rs
+expression: sql
+input_file: reference/tests/examples/language-features/dates_and_times-4.prql
+---
+SELECT
+  projects.*,
+  start + INTERVAL '10' DAY AS first_check_in
+FROM
+  projects