| g | on_ | x |
|---|---|---|
| a | 2026-01-02 | 10 |
| a | 2026-01-05 | 20 |
| b | 2026-01-06 | 30 |
17 Dates
What year was that, what month, and was it a weekday? A date usually arrives as text. to_date reads one, and once a column holds a date there is a word for each part you might want out of it.
The table is diary, three days held as text:
run('
diary
then add [d] as to_date([on_])
then add [year] as year([d]), [month] as month([d]), [weekday] as weekday([d])
')| g | on_ | x | d | year | month | weekday |
|---|---|---|---|---|---|---|
| a | 2026-01-02 | 10 | 2026-01-02 | 2026 | 1 | 5 |
| a | 2026-01-05 | 20 | 2026-01-05 | 2026 | 1 | 1 |
| b | 2026-01-06 | 30 | 2026-01-06 | 2026 | 1 | 2 |
diary |>
add(d = to_date(on_)) |>
add(year = year(d), month = month(d), weekday = weekday(d))| g | on_ | x | d | year | month | weekday |
|---|---|---|---|---|---|---|
| a | 2026-01-02 | 10 | 2026-01-02 | 2026 | 1 | 5 |
| a | 2026-01-05 | 20 | 2026-01-05 | 2026 | 1 | 1 |
| b | 2026-01-06 | 30 | 2026-01-06 | 2026 | 1 | 2 |
(diary
>> add(d = to_date(col.on_))
>> add(year = year(col.d), month = month(col.d), weekday = weekday(col.d)))| g | on_ | x | d | year | month | weekday |
|---|---|---|---|---|---|---|
| a | 2026-01-02 | 10 | 2026-01-02 | 2026 | 1 | 5 |
| a | 2026-01-05 | 20 | 2026-01-05 | 2026 | 1 | 1 |
| b | 2026-01-06 | 30 | 2026-01-06 | 2026 | 1 | 2 |
diary then add [d] as to_date([on_]) then add [year] as year([d]), [month] as month([d]), [weekday] as weekday([d])
weekday counts Monday as 1, wherever you run it. That is the grammar’s numbering rather than the engine’s, and it has to be: asked plainly, one engine calls a Friday 5 and another calls it 4, and neither says anything is wrong.
17.1 The five parts
year, month, day, weekday and hour. Each one pulls a number out of a date, and each is named for what it returns.
run('
diary
then add [on] as to_date([on_])
then add [y] as year([on]), [m] as month([on]), [d] as day([on]),
[wd] as weekday([on])
then pick [on_, y, m, d, wd]
')| on_ | y | m | d | wd |
|---|---|---|---|---|
| 2026-01-02 | 2026 | 1 | 2 | 5 |
| 2026-01-05 | 2026 | 1 | 5 | 1 |
| 2026-01-06 | 2026 | 1 | 6 | 2 |
diary |>
add(on = to_date(on_)) |>
add(y = year(on), m = month(on), d = day(on), wd = weekday(on)) |>
pick(on_, y, m, d, wd)| on_ | y | m | d | wd |
|---|---|---|---|---|
| 2026-01-02 | 2026 | 1 | 2 | 5 |
| 2026-01-05 | 2026 | 1 | 5 | 1 |
| 2026-01-06 | 2026 | 1 | 6 | 2 |
(diary
>> add(on = to_date(col.on_))
>> add(y = year(col.on), m = month(col.on), d = day(col.on),
wd = weekday(col.on))
>> pick(col.on_, col.y, col.m, col.d, col.wd))| on_ | y | m | d | wd |
|---|---|---|---|---|
| 2026-01-02 | 2026 | 1 | 2 | 5 |
| 2026-01-05 | 2026 | 1 | 5 | 1 |
| 2026-01-06 | 2026 | 1 | 6 | 2 |
hour is the fifth, and it is the one that needs a column carrying a time. The four above read parts of the calendar, which every date has. An hour is not part of the calendar, so a column has to have brought one with it.
stamps did. Its at column arrived as a timestamp rather than as a date:
| at |
|---|
| 2026-01-02 14:30:00 |
| 2026-03-15 09:05:00 |
run('
stamps
then add [h] as hour([at])
then pick [at, h]
')| at | h |
|---|---|
| 2026-01-02 14:30:00 | 14 |
| 2026-03-15 09:05:00 | 9 |
stamps |>
add(h = hour(at)) |>
pick(at, h)| at | h |
|---|---|
| 2026-01-02 14:30:00 | 14 |
| 2026-03-15 09:05:00 | 9 |
(stamps
>> add(h = hour(col.at))
>> pick(col.at, col.h))| at | h |
|---|---|
| 2026-01-02 14:30:00 | 14 |
| 2026-03-15 09:05:00 | 9 |
stamps then add [h] as hour([at]) then pick [at, h]
17.2 Why hour will not read a plain date
A column either carries a time or it does not, and the grammar knows which. Ask hour of one that does not and it refuses, rather than hand back a column of zeros that quietly assumed midnight:
collect(stamps |> add(h = hour(to_date(at))))Error:
!
illegal: `hour` reads the time of day a column carries, and a plain date carries none, so this would be nought on every row. It wants a column that arrived carrying one, which `to_date` does not make
|
2 | then add [h] as hour(to_date([at]))
| ^^^^^^^^^^^^^try:
collect(stamps >> add(h = hour(to_date(col.at))))
except GodError as refusal:
print(refusal)
illegal: `hour` reads the time of day a column carries, and a plain date carries none, so this would be nought on every row. It wants a column that arrived carrying one, which `to_date` does not make
|
2 | then add [h] as hour(to_date([at]))
| ^^^^^^^^^^^^^That refusal is the right shape. The sentence reads as though it should work, the answer would have been a number, and nothing about a column of zeros would have looked wrong on the page. Handing one back is what the grammar will not do.
to_date does not help, and it is the repair everybody reaches for. It makes a date, which is the thing that has no time in it, so the same refusal comes back:
collect(diary |> add(h = hour(to_date(on_))))Error:
!
illegal: `hour` reads the time of day a column carries, and a plain date carries none, so this would be nought on every row. It wants a column that arrived carrying one, which `to_date` does not make
|
2 | then add [h] as hour(to_date([on_]))
| ^^^^^^^^^^^^^^try:
collect(diary >> add(h = hour(to_date(col.on_))))
except GodError as refusal:
print(refusal)
illegal: `hour` reads the time of day a column carries, and a plain date carries none, so this would be nought on every row. It wants a column that arrived carrying one, which `to_date` does not make
|
2 | then add [h] as hour(to_date([on_]))
| ^^^^^^^^^^^^^^Splitting it over two steps does not help either, and the grammar says so in the same words: what it reads is the column, not the spelling that made it.
So a time has to reach the table as a time. Where the values are text, the conversion belongs to the language you are calling from, above the pipeline: R has as.POSIXct and Python has pd.to_datetime, and that is how at above became a timestamp before any of these examples ran.
The four calendar words are untouched by all of this. A year, a month, a day and a weekday are parts of the calendar, and every date has them whether or not a time came with it.
17.3 Text is not yet a date
The five words read a date, and text is not one until to_date has said so. Skip the conversion and the refusal points straight at it:
collect(diary |> add(y = year(on_)))Error:
!
illegal: `year` reads part of a date, and this is text. Convert it first with `to_date(...)`
|
2 | then add [y] as year([on_])
| ^^^try:
collect(diary >> add(y = year(col.on_)))
except GodError as refusal:
print(refusal)
illegal: `year` reads part of a date, and this is text. Convert it first with `to_date(...)`
|
2 | then add [y] as year([on_])
| ^^^That is the whole date vocabulary: to_date reads the text, five words take the part you ask for, and the answers agree wherever the sentence runs, Monday counting as 1 on every engine.