Skip to main content

Date- and time-related operations

Time units

The following are legal time units:

Time unitMeaning
MILLENNIUMA thousand years
CENTURYA hundred years; a number between 1 and 10
DECADETen years; a number between 1 and 10
YEAROne year; can be positive or negative
QUARTER1/4 of a year; a number between 1 and 4
MONTHOne month; a number between 1 and 12
WEEKSeven days. The definition of "week" is quite involved: The year's first week is the week containing the first Thursday of the year or either the week containing the 4th of January or either the week that begins between 29th of Dec. and 4th of Jan. The week number is thus a value between 0 and 53.
DOYDay of year, a number between 1 and 366
DOWDay of week, with Sunday being 1 and Saturday being 7
ISODOWISO day of the week, with Monday 1 and Sunday 7
DAYA day within a month, a number between 1 and 31
HOURAn hour within a day, a number between 0 and 23
MINUTEA minute within an hour, a number between 0 and 59
SECONDA second within a minute, a number between 0 and 59
MILLISECONDA millisecond within a minute, including the number of seconds multiplied by 1000, a number between 0 and 59,999
MICROSECONDA microsecond within a minute, including the number of seconds multiplied by 1,000,000, a number between 0 and 59,999,999
EPOCHNumber of seconds from Unix epoch, i.e., 1970/01/01.
SQL_TSI_YEARSame as YEAR
SQL_TSI_QUARTERSame as QUARTER
SQL_TSI_MONTHSame as MONTH
SQL_TSI_WEEKSame as WEEK
SQL_TSI_HOURSame as HOUR
SQL_TSI_DAYSame as DAY
SQL_TSI_MINUTESame as MINUTE
SQL_TSI_SECONDSame as SECOND

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

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.

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

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

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:

TypeExample literal
INTERVAL YEARINTERVAL '20' YEAR
INTERVAL YEAR TO MONTHINTERVAL '20-07' YEAR TO MONTH
INTERVAL MONTHINTERVAL '10' MONTH
INTERVAL DAYINTERVAL '10' DAY
INTERVAL DAY TO HOURINTERVAL '10 10' DAY TO HOUR
INTERVAL DAY TO MINUTEINTERVAL '10 10:30' DAY TO MINUTE
INTERVAL DAY TO SECONDINTERVAL '10 10:30:40.999' DAY TO SECOND
INTERVAL HOURINTERVAL '12' HOUR
INTERVAL HOUR TO MINUTEINTERVAL '12:10' HOUR TO MINUTE
INTERVAL HOUR TO SECONDINTERVAL '12:10:59' HOUR TO SECOND
INTERVAL MINUTEINTERVAL '10' MINUTE
INTERVAL MINUTE TO SECONDINTERVAL '80:01.001' MINUTE TO SECOND
INTERVAL SECONDINTERVAL '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 MILLENIUM and MONTHS.

Other date/time/timestamp/time interval operations

The following arithmetic operations are supported:

OperationResult TypeExplanation
DATE + INTERVALDATEAdd an interval to a date
INTERVAL + INTERVALINTERVALAdd two intervals; both must have the same type
TIMESTAMP + INTERVALTIMESTAMPAdd an interval to a timestamp
TIME + INTERVALTIMEAdd an interval to a time. Performs wrapping addition.
- INTERVALINTERVALNegate an interval
DATE - INTERVALDATESubtract an interval from a date
(TIME - TIME) TIMEUNITINTERVAL (short)Compute the difference between two times
TIME - INTERVALTIMESubtract an interval from a time. Performs wrapping subtraction.
TIMESTAMP - INTERVALTIMESTAMPSubtract an interval from a timestamp
INTERVAL - INTERVALINTERVALSubtract two intervals
INTERVAL * DOUBLEINTERVALMultiply an interval by a scalar
INTERVAL / DOUBLEINTERVALDivide an interval by a scalar
(DATE - DATE) TIMEUNITINTERVALSubtract two dates, generate an interval with the specified time units
(TIMESTAMP - TIMESTAMP) TIMEUNITINTERVALSubtract two timestamps, generate an interval with the specified time units

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.

Subtraction between two dates, timestamps, or times must be followed by a TIMEUNIT qualifier: e.g., (DATE '2024-01-01' - DATE '2023-12-31') 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.

The NOW function

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.

OperationDescriptionExample
NOWReturns a timestampNOW() => 2024-07-10 00:00:00
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).

Date formatting

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

OperationArgumentsExample
FORMAT_DATEstring_format, dateFORMAT_DATE("%Y=%m", d) => 2020-10

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

ElementTypesDescriptionExample
%AD,TSThe full weekday name (English)Wednesday
%aD,TSThe abbreviated weekday name (English)Wed
%BD,TSThe full month name (English)January
%bD,TSThe abbreviated month name (English)Jan
%CD,TSThe century (a year divided by 100 and truncated to an integer) as a decimal number (00-99)20
%cTSThe date and time representation (English)Wed Jan 20 21:47:00 2021
%DD, TSThe date in the format %m/%d/%y01/20/21
%dD, TSThe day of the month as a decimal number (01-31)20
%eD, TSThe day of the month as a decimal number (1-31); single digits are preceded by a space20
%FD, TSThe date in the format %Y-%m-%d2021-01-20
%GD, TSThe ISO 8601 year with century as a decimal number. Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %G and %Y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge2021
%gD, TSThe ISO 8601 year without century as a decimal number (00-99). Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %g and %y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge21
%HTS, TThe hour (24-hour clock) as a decimal number (00-23)21
%hD, TSSame as %bJan
%ITS, TThe hour (12-hour clock) as a decimal number (01-12)09
%jD, TSThe day of the year as a decimal number (001-366)020
%kTS, TThe hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space.21
%lTS, TThe hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space.9
%MTS, TThe minute as a decimal number (00-59)47
%mD, TSThe month as a decimal number (01-12)01
%PTS, TWhen formatting, this is either am or pm. This cannot be used with parsing. Instead, use %p.pm
%pTS, TWhen formatting, this is either AM or PM. When parsing, this can be used with am, pm, AM, or PM.PM
%RTS, TThe time in the format %H:%M21:47
%STS, TThe second as a decimal number (00-60)00
%sTS, TThe number of seconds since 1970-01-01 00:00:00.1611179220
%TTS, TThe time in the format %H:%M:%S21:47:00
%UD, TSThe week number of the year (Sunday as the first day of the week) as a decimal number (00-53)03
%uD, TSThe weekday (Monday as the first day of the week) as a decimal number (1-7)3
%VD, TSThe ISO 8601 week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 103
%WD, TSThe week number of the year (Monday as the first day of the week) as a decimal number (00-53)03
%wD, TSThe weekday (Sunday as the first day of the week) as a decimal number (0-6)3
%XTS, TThe time representation in HH:MM:SS format21:47:00
%xD, TSThe date representation in MM/DD/YY format01/20/21
%YD, TSThe year with century as a decimal number2021
%yD, TSThe year without century as a decimal number (00-99), with an optional leading zero.21
%ZTSThe time zone nameUTC-5
%zTSThe offset from the Prime Meridian in the format +HHMM or -HHMM as appropriate, with positive values representing locations east of Greenwich-0500
%nA newline character.
%tA tab character
%%A single % character%