Working with Dates
The tasks related to dates usually include three groups:
- Parsing dates
- Extracting parts of dates
- Match operations, like adding, subtracting, comparing, and finding the difference
Parsing dates
If a date or timestamp is represented as a string, you need to convert it to the timestamp type before starting to work with it. For such conversion, you can use the function as_timestamp.
The function accepts one required argument (input string) and an optional layout used to parse non-typical date representations.
The layouts can vary and depend on the input date format:
- Jan 2, 2006 15:04:05
- January 2, 06 15:04:04
| Element | Possible values |
|---|---|
| Year | "2006", "06" |
| Month | "Jan", "January", "01", "1" |
| Day of the week | "Mon", "Monday" |
| Day of the month | "2", "_2", "02" |
| Day of the year | "__2", "002" |
| Hour | "15", "3", "03" (PM or AM) |
| Minute | "4", "04" |
| Second | "5", "05" |
| AM/PM mark | "PM" |
as_timestamp(`date`, "Jan 2, 2006 15:04:05")
as_timestamp(`date`, "02/01/06")
Once the string value is converted to a timestamp value, you can apply any date-related functions.
Extracting parts of dates
There are many functions that can help you to select part of date, for example:
year(`date`)
month(`date`)
day(`date`)
weekday(`date`)
hour(`date`)
minute(`date`)