Date- and time-related operations
Time units
The following are legal time units:
| Time unit | Meaning |
|---|---|
MILLENNIUM | A thousand years |
CENTURY | A hundred years; a number between 1 and 10 |
DECADE | Ten years; a number between 1 and 10 |
YEAR | One year; can be positive or negative |
QUARTER | 1/4 of a year; a number between 1 and 4 |
MONTH | One month; a number between 1 and 12 |
WEEK | Seven 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. |
DOY | Day of year, a number between 1 and 366 |
DOW | Day of week, with Sunday being 1 and Saturday being 7 |
ISODOW | ISO day of the week, with Monday 1 and Sunday 7 |
DAY | A day within a month, a number between 1 and 31 |
HOUR | An hour within a day, a number between 0 and 23 |
MINUTE | A minute within an hour, a number between 0 and 59 |
SECOND | A second within a minute, a number between 0 and 59 |
MILLISECOND | A millisecond within a minute, including the number of seconds multiplied by 1000, a number between 0 and 59,999 |
MICROSECOND | A microsecond within a minute, including the number of seconds multiplied by 1,000,000, a number between 0 and 59,999,999 |
EPOCH | Number of seconds from Unix epoch, i.e., 1970/01/01. |
SQL_TSI_YEAR | Same as YEAR |
SQL_TSI_QUARTER | Same as QUARTER |
SQL_TSI_MONTH | Same as MONTH |
SQL_TSI_WEEK | Same as WEEK |
SQL_TSI_HOUR | Same as HOUR |
SQL_TSI_DAY | Same as DAY |
SQL_TSI_MINUTE | Same as MINUTE |
SQL_TSI_SECOND | Same 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
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)
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 |
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 - TIME | INTERVAL (short) | Compute the difference between two times |
TIME - INTERVAL | TIME | Subtract an interval from a time. Performs wrapping subtraction. |
TIMESTAMP - INTERVAL | TIMESTAMP | Subtract an interval from a timestamp |
INTERVAL - INTERVAL | INTERVAL | Subtract two intervals |
INTERVAL * DOUBLE | INTERVAL | Multiply an interval by a scalar |
INTERVAL / DOUBLE | INTERVAL | Divide an interval by a scalar |
TIMESTAMP - TIMESTAMP | INTERVAL (long) | Subtract two timestamps, convert result into days |
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.
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.
| 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 formatting
We support the following functions for formatting date-like values:
| Operation | Arguments | Example |
|---|---|---|
FORMAT_DATE | string_format, date | FORMAT_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
| Element | Types | Description | Example |
|---|---|---|---|
| %A | D,TS | The full weekday name (English) | Wednesday |
| %a | D,TS | The abbreviated weekday name (English) | Wed |
| %B | D,TS | The full month name (English) | January |
| %b | D,TS | The abbreviated month name (English) | Jan |
| %C | D,TS | The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99) | 20 |
| %c | TS | The date and time representation (English) | Wed Jan 20 21:47:00 2021 |
| %D | D, TS | The date in the format %m/%d/%y | 01/20/21 |
| %d | D, TS | The day of the month as a decimal number (01-31) | 20 |
| %e | D, TS | The day of the month as a decimal number (1-31); single digits are preceded by a space | 20 |
| %F | D, TS | The date in the format %Y-%m-%d | 2021-01-20 |
| %G | D, TS | The 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 diverge | 2021 |
| %g | D, TS | The 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 diverge | 21 |
| %H | TS, T | The hour (24-hour clock) as a decimal number (00-23) | 21 |
| %h | D, TS | Same as %b | Jan |
| %I | TS, T | The hour (12-hour clock) as a decimal number (01-12) | 09 |
| %j | D, TS | The day of the year as a decimal number (001-366) | 020 |
| %k | TS, T | The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space. | 21 |
| %l | TS, T | The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space. | 9 |
| %M | TS, T | The minute as a decimal number (00-59) | 47 |
| %m | D, TS | The month as a decimal number (01-12) | 01 |
| %P | TS, T | When formatting, this is either am or pm. This cannot be used with parsing. Instead, use %p. | pm |
| %p | TS, T | When formatting, this is either AM or PM. When parsing, this can be used with am, pm, AM, or PM. | PM |
| %R | TS, T | The time in the format %H:%M | 21:47 |
| %S | TS, T | The second as a decimal number (00-60) | 00 |
| %s | TS, T | The number of seconds since 1970-01-01 00:00:00. | 1611179220 |
| %T | TS, T | The time in the format %H:%M:%S | 21:47:00 |
| %U | D, TS | The week number of the year (Sunday as the first day of the week) as a decimal number (00-53) | 03 |
| %u | D, TS | The weekday (Monday as the first day of the week) as a decimal number (1-7) | 3 |
| %V | D, TS | The 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 1 | 03 |
| %W | D, TS | The week number of the year (Monday as the first day of the week) as a decimal number (00-53) | 03 |
| %w | D, TS | The weekday (Sunday as the first day of the week) as a decimal number (0-6) | 3 |
| %X | TS, T | The time representation in HH:MM:SS format | 21:47:00 |
| %x | D, TS | The date representation in MM/DD/YY format | 01/20/21 |
| %Y | D, TS | The year with century as a decimal number | 2021 |
| %y | D, TS | The year without century as a decimal number (00-99), with an optional leading zero. | 21 |
| %Z | TS | The time zone name | UTC-5 |
| %z | TS | The offset from the Prime Meridian in the format +HHMM or -HHMM as appropriate, with positive values representing locations east of Greenwich | -0500 |
| %n | A newline character. | ||
| %t | A tab character | ||
| %% | A single % character | % |