There are numerous date and time types and functions available in the SQL spec to help reason about time. Below are a few that cover many of the cases needed when querying temporal data.
TIMESTAMP function
We’ve used a literal TIMESTAMP
in previous examples, which requires a full datetime expression. Using the TIMESTAMP
function, we can specify a date or datetime, adding some flexibility to how specific we want to define our queries.
NOW function
Use NOW()
to get the current datetime. Useful when paired with INTERVAL
(described below).
INTERVAL keyword
The INTERVAL
keyword can be used to add to or subtract from temporal types. To use an interval, specify the quantity and unit of the interval, such as INTERVAL 1 MONTH
.
Some interval units include: MICROSECOND
, SECOND
, MINUTE
, HOUR
, DAY
, WEEK
, MONTH
, QUARTER
, and YEAR
.
Composite units are also supported, such as: INTERVAL '1:30' HOUR_MINUTE