Working with Timestamps
Working with DATE and TIMESTAMP often involves working with INTERVALs.
INTERVALs may not always act as expected, especially when working with months and years, primarily due to complexities in quickly and accurately determining if an number of days is a given number of months.
Actions
Add/Subtract
timestamp +
interval → timestamp
timestamp -
interval → timestamp
timestamp -
timestamp → interval *
interval +
interval -> interval
interval -
interval -> interval
timestamp +
timestamp -> not possible
DATEDIFF
(unit: varchar, start: timestamp, end: timestamp) → numeric
Note INTERVALs created as the result of date and timestamp substraction have no month or year component and are handled internally as seconds. This may result in unexpected outcomes, for example when mixed with month calculations.
If determing differences in months or years, this form is supported:
this form is not
Construct
Examples:
INTERVAL '1' YEAR
INTERVAL '1' DAY
INTERVAL '1 1' DAY TO HOUR
Supported units:
YEAR
, MONTH
, DAY
, HOUR
, MINUTE
, SECOND
Examples:
TIMESTAMP '2024-02-14'
Extract
Format
Parse
Truncate
Generate
Note that current_date
and current_time
support being called without parenthesis.
Recognized date parts and periods and support across various functions:
Part | DATE_TRUNC | EXTRACT | DATEDIFF | TIME_BUCKET | Notes |
---|---|---|---|---|---|
second | ✓ | ✓ | ✓ | ✓ | |
minute | ✓ | ✓ | ✓ | ✓ | |
hour | ✓ | ✓ | ✓ | ✓ | |
day | ✓ | ✓ | ✓ | ✓ | |
dow | ✘ | ✓ | ✘ | ✘ | day of week |
week | ✓ | ✓ | ✓ | ✓ | iso week i.e. to monday |
month | ✓ | ✓ | ▲ | ✓ | DATEFIFF unreliable calculating months |
quarter | ✓ | ✓ | ✓ | ✓ | |
doy | ✘ | ✓ | ✘ | ✘ | day of year |
year | ✓ | ✓ | ✓ | ✓ |
Implicit Casting
In many situation where a timestamp is expected, if an RFC 3339 formatted string is provided, the engine will interpret as a timestamp.
Timezones
The engine is opinionated to run in UTC - all instances where the system time is requested, UTC is used.
Precision
INTERVALs are maintained internally with a millisecond precision.
TIMESTAMPs are maintained internally with a nanosecond precision.
DATEs are maintained internally with a day precision.