The following are legal time units:

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 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.

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.

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 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_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.

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 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:

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 TIMESTAMP value produces a TIMESTAMP result
• Adding a interval of hours, minutes, or seconds to a DATE produces a TIMESTAMP result
• Adding a an interval of days, months, or longer to a DATE produces a DATE result

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');

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 (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:

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.

The following arithmetic operations are supported:

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.

DATE, TIME and TIMESTAMP have no time zone.

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.

:::warning

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).

We support the following functions for formatting and parsing date-like values:

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 NULL if 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

These are currently unused

It is possible to override the default padding behavior of numeric specifiers %?. This is not allowed for other specifiers.