Date- and Time-Related Operations
Time units
The following are legal time units:
Dates
The date type represents a Gregorian calendar date, independent of time zone. This extends to dates before the Gregorian calendar was introduced, effectively meaning that the dates use a Proleptic calendar.
Date literals
DATE literals have the form DATE 'YYYY-MM-DD'. Trailing spaces
are not allowed.
Date literals can only represent 4-digit year positive values. Values BC or values greater than 10,000 years are not supported.
Date operations
The following operations are available on dates:
DATE_TRUNC(date, <unit>), where <unit> is a time unit, as
described above, between MILLENNIUM and DAY. Result is a DATE.
Rounds down the date to the specified time unit. Example:
DATE_TRUNC('2020-01-10', MONTH) produces the result
2020-01-01.
EXTRACT(<unit> FROM date) where <unit> is a time unit, as
described above. Result is always a BIGINT value.
DATE_PART is a synonym for EXTRACT.
The following abbreviations can be used as well:
YEAR(date) is an abbreviation for EXTRACT(YEAR FROM date).
MONTH(date) is an abbreviation for EXTRACT(MONTH FROM date).
DAYOFMONTH(date) is an abbreviation for EXTRACT(DAY FROM date).
DAYOFWEEK(date) is an abbreviation for EXTRACT(DOW FROM date).
HOUR(date) is an abbreviation for EXTRACT(HOUR FROM date).
For dates it always returns 0, since dates have no time component.
MINUTE(date) is an abbreviation for EXTRACT(MINUTE FROM date).
For dates it always returns 0, since dates have no time component.
SECOND(date) is an abbreviation for EXTRACT(SECOND FROM date).
For dates it always returns 0, since dates have no time component.
FLOOR(date TO <unit>), where <unit> is a time unit.
CEIL(date TO <unit>), where <unit> is a time unit.
Values of type DATE can be compared using =, <>, !=, <, >,
<=, >=, <=>, BETWEEN; the result is a Boolean.
TIMESTAMPDIFF(<unit>, left, right) computes the difference between
two dates values and expresses the result in the specified time units.
The result is a 32-bit integer.
Times
A time represents the time of day, a value between 0 and 24 hours (excluding the latter).
The TIME data type can specify an optional precision, e.g.,
TIME(2). The precision is the number of sub-second digits
supported. So TIME(3) is a time with a precision of milliseconds.
The default precision is 3.
Currently the maximum supported precision is 3 (milliseconds). Larger precisions are accepted, but internally only up to 3 digits of precision are maintained.
Time literals
TIME literals have the form TIME 'HH:MM:SS.FFF', where the
fractional part is optional, and can have between 0 and 3 digits. An
example is: '23:59:59.132'. The hours must be between 0 and 23, the
minutes between 0 and 59, and the seconds between 0 and 59. Exactly
two digits must be used for hours, minutes, and seconds. Spaces are
not allowed between quotes.
Time operations
TIME_TRUNC(time, <unit>), where <unit> is a time unit,
as described above, between HOUR and SECOND. Result is a
TIME. Rounds down the time to the specified time unit.
Example: TIME_TRUNC('12:34:56.78', MINUTE) produces the
result 12:34:00.
EXTRACT(<unit> FROM time) where <unit> is a time unit from
HOUR, MINUTE, SECOND, MILLISECOND; the semantics is as
described above. Result is always a BIGINT value.
The following abbreviations can be used as well:
HOUR(time) is an abbreviation for EXTRACT(HOUR FROM time).
MINUTE(time) is an abbreviation for EXTRACT(MINUTE FROM time).
SECOND(time) is an abbreviation for EXTRACT(SECOND FROM time).
Values of type TIME can be compared using =, <>, !=, <, >,
<=, >=, <=>, BETWEEN; the result is a Boolean.
TIMESTAMPDIFF(<unit>, left, right) computes the difference between
two time values and expresses the result in the specified time units.
The result is a 32-bit integer.
Timestamps
The TIMESTAMP data type represents values composed of a DATE (as
described above) and a TIME. TIMESTAMP support an optional
precision specification, e.g.: TIMESTAMP(3). The precision applies
to the TIME component of the TIMESTAMP. The maximum precision
supported for timestamps is 3. The default precision for timestamps
(used when no precision is specified), is also 3.
Timestamp literals
TIMESTAMP literals have the form TIMESTAMP 'YYYY-MM-DD HH:MM:SS.FFF', where the fractional part is optional. Trailing
spaces are not allowed.
Timestamp literals can only represent 4-digit year positive values. Values BC or values greater than 10,000 years are not supported.
The following operations are available on timestamps:
Operations on timestamps
A cast from a numeric value to a TIMESTAMP interprets the numeric
value as an (big integer) number of milliseconds since the Unix epoch.
Conversely, a cast from a TIMESTAMP to a numeric value retrieves the
number of milliseconds since the Unix epoch from the timestamp.
TIMESTAMP_TRUNC(timestamp, <unit>), where <unit> is a time unit,
as described above, between MILLENNIUM and SECOND. Result is a
TIMESTAMP. Rounds down the timestamp to the specified time unit.
Example: TIMESTAMP_TRUNC('2020-01-10 10:00:00', MONTH) produces the
result 2020-01-01 00:00:00.
EXTRACT(<unit> FROM timestamp) where <unit> is a time unit, as
described above. Result is always a BIGINT value.
The following abbreviations can be used as well:
YEAR(timestamp) is an abbreviation for EXTRACT(YEAR FROM timestamp).
MONTH(timestamp) is an abbreviation for EXTRACT(MONTH FROM timestamp).
DAYOFMONTH(timestamp) is an abbreviation for EXTRACT(DAY FROM timestamp).
DAYOFWEEK(timestamp) is an abbreviation for EXTRACT(DOW FROM timestamp).
HOUR(timestamp) is an abbreviation for EXTRACT(HOUR FROM timestamp).
MINUTE(timestamp) is an abbreviation for EXTRACT(MINUTE FROM timestamp).
SECOND(timestamp) is an abbreviation for EXTRACT(SECOND FROM timestamp).
FLOOR(timestamp TO <unit>), where <unit> is a time unit.
CEIL(timestamp TO <unit>), where <unit> is a time unit.
Values of type TIMESTAMP can be compared using =, <>, !=, <,
>, <=, >=, <=>, BETWEEN; the result is a Boolean.
TIMESTAMPDIFF(<unit>, left, right) computes the difference between
two timestamps and expresses the result in the specified time units.
The result is a 32-bit integer. DATEDIFF is a synonym for
TIMESTAMPDIFF. One month is considered elapsed when the calendar
month has increased and the calendar day and time is greater than or equal
to the start. Weeks, quarters, and years follow from that.
TIMESTAMPADD(<unit>, integer, timestamp) adds an interval in the
specified unit to a timestamp.
DATEADD is a synonym for
TIMESTAMPADD. The added value can be negative. The type of the
result is as follows:
- Adding anything to a
TIMESTAMPvalue produces aTIMESTAMPresult - Adding a interval of hours, minutes, or seconds to a
DATEproduces aTIMESTAMPresult - Adding a an interval of days, months, or longer to a
DATEproduces aDATEresult
To create a timestamp using the Unix EPOCH in seconds as a base, you
can use the TIMESTAMPADD function. The following code creates a
MAKE_TIMESTAMP function which creates a TIMESTAMP given a number of
seconds:
CREATE FUNCTION MAKE_TIMESTAMP(SECONDS BIGINT) RETURNS TIMESTAMP AS
TIMESTAMPADD(SECOND, SECONDS, DATE '1970-01-01');
Time intervals
The interval types
Note that currently one cannot specify a type of INTERVAL for a
table column. Interval types can be generated by queries though, so
they can appear in the computed views.
Interval literals
Interval literals (constant values) can be written using the following verbose syntax:
INTERVAL 'string' timeUnit [ TO timeUnit]
tiemUnit is one of millisecond, second, minute, hour, day,
week, month, quarter, year, or plurals of these units. Only
the following combinations are supported:
| Type | Example literal |
|---|---|
INTERVAL YEAR | INTERVAL '20' YEAR |
INTERVAL YEAR TO MONTH | INTERVAL '20-07' YEAR TO MONTH |
INTERVAL MONTH | INTERVAL '10' MONTH |
INTERVAL DAY | INTERVAL '10' DAY |
INTERVAL DAY TO HOUR | INTERVAL '10 10' DAY TO HOUR |
INTERVAL DAY TO MINUTE | INTERVAL '10 10:30' DAY TO MINUTE |
INTERVAL DAY TO SECOND | INTERVAL '10 10:30:40.999' DAY TO SECOND |
INTERVAL HOUR | INTERVAL '12' HOUR |
INTERVAL HOUR TO MINUTE | INTERVAL '12:10' HOUR TO MINUTE |
INTERVAL HOUR TO SECOND | INTERVAL '12:10:59' HOUR TO SECOND |
INTERVAL MINUTE | INTERVAL '10' MINUTE |
INTERVAL MINUTE TO SECOND | INTERVAL '80:01.001' MINUTE TO SECOND |
INTERVAL SECOND | INTERVAL '80.001' SECOND |
A leading negative sign applies to all fields; for example the
negative sign in the interval literal INTERVAL '-1 2:03:04' DAYS TO SECONDS applies to both the days and hour/minute/second parts.
To specify an interval value with more than 2 digits you must specify
an increased precision for the corresponding type, e.g.:
INTERVAL '100' HOUR(3)
EXTRACT(unit FROM interval) extracts the specified value from an INTERVAL.
Short intervals support EXTRACT of values between DAYS and MILLISECONDS.
Long intervals support EXTRACT of values between MILLENIUM and MONTHS.
Other date/time/timestamp/time interval operations
The following arithmetic operations are supported:
| Operation | Result Type | Explanation |
|---|---|---|
date + interval | DATE | Add an interval to a date |
(date - date) shortInterval | INTERVAL | Compute the difference between two dates as a short interval |
(date - date) longInterval | INTERVAL | Compute the difference between two dates as a long interval |
(time - time) shortInterval | INTERVAL | Compute the difference between two times as a short interval |
interval + interval | INTERVAL | Add two intervals; both must have the same type |
timestamp + interval | TIMESTAMP | Add an interval to a timestamp |
time + interval | TIME | Add an interval to a time. Performs wrapping addition. |
- interval | INTERVAL | Negate an interval |
date - interval | DATE | Subtract an interval from a date |
time - interval | TIME | Subtract an interval from a time. Performs wrapping subtraction. |
timestamp - interval | TIMESTAMP | Subtract an interval from a timestamp |
(timestamp - timestamp) shortInterval | INTERVAL | Compute the difference between two timestamps as a short interval |
(TIMESTAMP - TIMESTAMP) longInterval | INTERVAL | Compute the difference between two timestamps as a long interval |
interval - interval | INTERVAL | Subtract two intervals |
interval * double | INTERVAL | Multiply an interval by a scalar |
interval / double | INTERVAL | Divide an interval by a scalar |
Arithmetic involving a TIME value always produces a (positive)
TIME value, between 00:00:00 (inclusive) and 24:00:00
(exclusive). One can think of the computation as being performed in
nanoseconds, and then performing a modulo operation with the number of
nanoseconds in a day. For this reason, adding or subtracting a long
interval from a TIME value is supported, but always leaves the
TIME value unchanged (since long intervals always consist of a whole
number of days).
Arithmetic between a DATE and an INTERVAL first converts the
interval to a whole number days (rounding down) and then performs the
computation on whole days.
DATE_SUB is a synonim for DATE - INTERVAL.
DATE_ADD is a synonim for DATE + INTERVAL.
Timezones
DATE, TIME and TIMESTAMP have no time zone.
NOW
The NOW() function returns the current date and time as a
TIMESTAMP value. More precisely, it returns the date and time
when the current step of the pipeline was triggered. A step
is triggered when the pipeline receives one or more new inputs or after a
user-configurable period of time if no new inputs arrive.
When executing a step, the pipeline incrementally updates all its views.
In particular, views that depend on the value of NOW() are updated
using the new current time. The value of NOW() remains constant within a
step.
By default, in the absence of new inputs, a step is triggered every
100 milliseconds. This behavior is controlled by the
clock_resolution_usecs pipeline configuration setting.
| Operation | Description | Example |
|---|---|---|
NOW | Returns a timestamp | NOW() => 2024-07-10 00:00:00 |
Programs that use NOW() can be very inefficient. For example, a
program such as SELECT T.x + NOW() FROM T has to scan the
entire table T at every step. Use this function judiciously.
Note however that a specific class of WHERE and HAVING expressions
that use NOW() can be implemented very efficiently. These are the
so-called "temporal filters". Here is an example:
SELECT * FROM T WHERE T.ts >= NOW() - INTERVAL 1 DAYS;
In general, a temporal filter will involve inequality or equality
comparisons between an expression and a monotone function of the NOW
result. A conjunction of such terms is also accepted if all terms
involve the same expression (e.g.: T.ts >= NOW() - INTERVAL 1 DAYS AND T.ts <= NOW() + INTERVAL 1 DAYS).
Date parsing and formatting
We support the following functions for formatting and parsing date-like values:
| Operation | Arguments | Result | Example |
|---|---|---|---|
FORMAT_DATE | string_format, date | string | FORMAT_DATE("%Y-%m", d) => 2020-10 |
PARSE_DATE | string_format, string | DATE | PARSE_DATE(" %Y-%m-%d", ' 2020-10-01') => 2020-10-01 |
PARSE_TIME | string_format, string | TIME | PARSE_TIME("%H:%m", '10:10') => 10:10:00 |
PARSE_TIMESTAMP | string_format, string | TIMESTAMP | PARSE_TIMESTAMP("%Y-%m", '2020-10') => 2020-10-01 00:00:00 |
If the string cannot be parsed according to the specified format:
- a runtime error is produced if the format is incorrect for producing a value of the required type
- the result is
NULLif string cannot be parsed according to the format
For the parsing functions the format string must have enough elements
to obtain all elements required for building the result. For example,
PARSE_TIME('%I:%M:%S', '10:00:00') will produce a runtime error,
since the %I element does not have enough information to infer
whether the hour is AM or PM (an extra %p element is needed).
The format string recognizes the following format specifiers; this
definition follows the Rust strftime spec:
https://docs.rs/chrono/latest/chrono/format/strftime/index.html
Date Specifiers
| Spec. | Example | Description |
|---|---|---|
| %Y | 2001 | The full proleptic Gregorian year, zero-padded to 4 digits. chrono supports years from -262144 to 262143. Note: years before 1 BCE or after 9999 CE, require an initial sign (+/-). |
| %C | 20 | The proleptic Gregorian year divided by 100, zero-padded to 2 digits. |
| %y | 01 | The proleptic Gregorian year modulo 100, zero-padded to 2 digits. |
| %m | 07 | Month number (01–12), zero-padded to 2 digits. |
| %b | Jul | Abbreviated month name. Always 3 letters. |
| %B | July | Full month name. Also accepts corresponding abbreviation in parsing. |
| %h | Jul | Same as %b. |
| %d | 08 | Day number (01–31), zero-padded to 2 digits. |
| %e | 8 | Same as %d but space-padded. Same as %_d. |
| %a | Sun | Abbreviated weekday name. Always 3 letters. |
| %A | Sunday | Full weekday name. Also accepts corresponding abbreviation in parsing. |
| %w | 0 | Sunday = 0, Monday = 1, …, Saturday = 6. |
| %u | 7 | Monday = 1, Tuesday = 2, …, Sunday = 7. (ISO 8601) |
| %U | 28 | Week number starting with Sunday (00–53), zero-padded to 2 digits. |
| %W | 27 | Same as %U, but week 1 starts with the first Monday in that year instead. |
| %G | 2001 | Same as %Y but uses the year number in ISO 8601 week date. |
| %g | 01 | Same as %y but uses the year number in ISO 8601 week date. |
| %V | 27 | Same as %U but uses the week number in ISO 8601 week date (01–53). |
| %j | 189 | Day of the year (001–366), zero-padded to 3 digits. |
| %D | 07/08/01 | Month-day-year format. Same as %m/%d/%y. |
| %F | 2001-07-08 | Year-month-day format (ISO 8601). Same as %Y-%m-%d. |
| %v | 8-Jul-2001 | Day-month-year format. Same as %e-%b-%Y. |
Time Specifiers
| Spec. | Example | Description |
|---|---|---|
| %H | 00 | Hour number (00–23), zero-padded to 2 digits. |
| %k | 0 | Same as %H but space-padded. Same as %_H. |
| %I | 12 | Hour number in 12-hour clocks (01–12), zero-padded to 2 digits. |
| %l | 12 | Same as %I but space-padded. Same as %_I. |
| %P | am | am or pm in 12-hour clocks. |
| %p | AM | AM or PM in 12-hour clocks. |
| %M | 34 | Minute number (00–59), zero-padded to 2 digits. |
| %S | 60 | Second number (00–60), zero-padded to 2 digits. |
| %f | 26490000 | Number of nanoseconds since last whole second. |
| %.f | .026490 | Decimal fraction of a second. Consumes the leading dot. |
| %.3f | .026 | Decimal fraction of a second with a fixed length of 3. |
| %.6f | .026490 | Decimal fraction of a second with a fixed length of 6. |
| %.9f | .026490000 | Decimal fraction of a second with a fixed length of 9. |
| %3f | 026 | Decimal fraction of a second like %.3f but without the leading dot. |
| %6f | 026490 | Decimal fraction of a second like %.6f but without the leading dot. |
| %9f | 026490000 | Decimal fraction of a second like %.9f but without the leading dot. |
| %R | 00:34 | Hour-minute format. Same as %H:%M. |
| %T | 00:34:60 | Hour-minute-second format. Same as %H:%M:%S. |
Time Zone Specifiers
These are currently unused
| Spec. | Example | Description |
|---|---|---|
| %Z | ACST | Local time zone name. Skips all non-whitespace characters during parsing. Identical to %:z when formatting. |
| %z | +0930 | Offset from the local time to UTC (with UTC being +0000). |
| %:z | +09:30 | Same as %z but with a colon. |
| %::z | +09:30:00 | Offset from the local time to UTC with seconds. |
| %:::z | +09 | Offset from the local time to UTC without minutes. |
| %#z | +09 | Parsing only: Same as %z but allows minutes to be missing or present. |
Timestamp Specifiers
| Spec. | Example | Description |
|---|---|---|
| %+ | 2001-07-08T00:34:60.026490+09:30 | ISO 8601 / RFC 3339 date & time format. |
| %s | 994518299 | UNIX timestamp, the number of seconds since 1970-01-01 00:00 UTC. |
Special Specifiers
| Spec. | Example | Description |
|---|---|---|
| %t | Literal tab (\t). | |
| %n | Literal newline (\n). | |
| %% | Literal percent sign. |
It is possible to override the default padding behavior of numeric specifiers %?. This is not allowed for other specifiers.
| Modifier | Description |
|---|---|
| %-? | Suppresses any padding including spaces and zeroes. (e.g. %j = 012, %-j = 12) |
| %_? | Uses spaces as a padding. (e.g. %j = 012, %_j = 12) |
| %0? | Uses zeroes as a padding. (e.g. %e = 9, %0e = 09) |