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(datetime TO <unit>), where <unit> is a time unit.

CEIL(datetime TO <unit>), where <unit> is a time unit.

Values of type DATE can be compared using =, <>, !=, <, >, <=, >=, <=>, BETWEEN; the result is a Boolean.

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.

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:

DATE_TRUNC(timestamp, <unit>), where <unit> is a time unit, as described above, between MILLENNIUM and DAY. Result is a TIMESTAMP. Rounds down the timestamp to the specified time unit. Example: DATE_TRUNC('2020-01-10 10:00:00', MONTH) produces the result 2020-01-01 00:00:00.

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

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)

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, 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 date-like values:

These functions are similar to the BigQuery functions: https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time

The format string recognizes the following format specifiers: (the Types column encodes the following types: D=Date, TS=TIMESTAMP, T=TIME