functions_datetime.yaml¶

This document file is generated for functions_datetime.yaml

Scalar Functions¶

extract¶

Implementations:
extract(component, x, timezone): -> return_type

x: Timezone string from IANA tzdb.

0. extract(component, timestamp_tz, string): -> i64
1. extract(component, precision_timestamp_tz, string): -> i64
2. extract(component, timestamp): -> i64
3. extract(component, precision_timestamp): -> i64
4. extract(component, date): -> i64
5. extract(component, time): -> i64
6. extract(component, indexing, timestamp_tz, string): -> i64
7. extract(component, indexing, precision_timestamp_tz, string): -> i64
8. extract(component, indexing, timestamp): -> i64
9. extract(component, indexing, precision_timestamp): -> i64
10. extract(component, indexing, date): -> i64

Extract portion of a date/time value. * YEAR Return the year. * ISO_YEAR Return the ISO 8601 week-numbering year. First week of an ISO year has the majority (4 or more) of its days in January. * US_YEAR Return the US epidemiological year. First week of US epidemiological year has the majority (4 or more) of its days in January. Last week of US epidemiological year has the year’s last Wednesday in it. US epidemiological week starts on Sunday. * QUARTER Return the number of the quarter within the year. January 1 through March 31 map to the first quarter, April 1 through June 30 map to the second quarter, etc. * MONTH Return the number of the month within the year. * DAY Return the number of the day within the month. * DAY_OF_YEAR Return the number of the day within the year. January 1 maps to the first day, February 1 maps to the thirty-second day, etc. * MONDAY_DAY_OF_WEEK Return the number of the day within the week, from Monday (first day) to Sunday (seventh day). * SUNDAY_DAY_OF_WEEK Return the number of the day within the week, from Sunday (first day) to Saturday (seventh day). * MONDAY_WEEK Return the number of the week within the year. First week starts on first Monday of January. * SUNDAY_WEEK Return the number of the week within the year. First week starts on first Sunday of January. * ISO_WEEK Return the number of the ISO week within the ISO year. First ISO week has the majority (4 or more) of its days in January. ISO week starts on Monday. * US_WEEK Return the number of the US week within the US year. First US week has the majority (4 or more) of its days in January. US week starts on Sunday. * HOUR Return the hour (0-23). * MINUTE Return the minute (0-59). * SECOND Return the second (0-59). * MILLISECOND Return number of milliseconds since the last full second. * MICROSECOND Return number of microseconds since the last full millisecond. * NANOSECOND Return number of nanoseconds since the last full microsecond. * PICOSECOND Return number of picoseconds since the last full nanosecond. * SUBSECOND Return number of microseconds since the last full second of the given timestamp. * UNIX_TIME Return number of seconds that have elapsed since 1970-01-01 00:00:00 UTC, ignoring leap seconds. * TIMEZONE_OFFSET Return number of seconds of timezone offset to UTC. The range of values returned for QUARTER, MONTH, DAY, DAY_OF_YEAR, MONDAY_DAY_OF_WEEK, SUNDAY_DAY_OF_WEEK, MONDAY_WEEK, SUNDAY_WEEK, ISO_WEEK, and US_WEEK depends on whether counting starts at 1 or 0. This is governed by the indexing option. When indexing is ONE: * QUARTER returns values in range 1-4 * MONTH returns values in range 1-12 * DAY returns values in range 1-31 * DAY_OF_YEAR returns values in range 1-366 * MONDAY_DAY_OF_WEEK and SUNDAY_DAY_OF_WEEK return values in range 1-7 * MONDAY_WEEK, SUNDAY_WEEK, ISO_WEEK, and US_WEEK return values in range 1-53 When indexing is ZERO: * QUARTER returns values in range 0-3 * MONTH returns values in range 0-11 * DAY returns values in range 0-30 * DAY_OF_YEAR returns values in range 0-365 * MONDAY_DAY_OF_WEEK and SUNDAY_DAY_OF_WEEK return values in range 0-6 * MONDAY_WEEK, SUNDAY_WEEK, ISO_WEEK, and US_WEEK return values in range 0-52 The indexing option must be specified when the component is QUARTER, MONTH, DAY, DAY_OF_YEAR, MONDAY_DAY_OF_WEEK, SUNDAY_DAY_OF_WEEK, MONDAY_WEEK, SUNDAY_WEEK, ISO_WEEK, or US_WEEK. The indexing option cannot be specified when the component is YEAR, ISO_YEAR, US_YEAR, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, SUBSECOND, UNIX_TIME, or TIMEZONE_OFFSET. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

Options:

component ['HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND', 'SUBSECOND']

component ['QUARTER', 'MONTH', 'DAY', 'DAY_OF_YEAR', 'MONDAY_DAY_OF_WEEK', 'SUNDAY_DAY_OF_WEEK', 'MONDAY_WEEK', 'SUNDAY_WEEK', 'ISO_WEEK', 'US_WEEK']

component ['YEAR', 'ISO_YEAR', 'US_YEAR', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND', 'NANOSECOND', 'PICOSECOND', 'SUBSECOND', 'UNIX_TIME', 'TIMEZONE_OFFSET']

component ['YEAR', 'ISO_YEAR', 'US_YEAR', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND', 'NANOSECOND', 'SUBSECOND', 'UNIX_TIME']

component ['YEAR', 'ISO_YEAR', 'US_YEAR', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND', 'SUBSECOND', 'PICOSECOND', 'UNIX_TIME', 'TIMEZONE_OFFSET']

component ['YEAR', 'ISO_YEAR', 'US_YEAR', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND', 'SUBSECOND', 'UNIX_TIME']

component ['YEAR', 'ISO_YEAR', 'US_YEAR', 'UNIX_TIME']

indexing ['ONE', 'ZERO']

extract_boolean¶

Implementations:
extract_boolean(component, x): -> return_type
0. extract_boolean(component, timestamp): -> boolean
1. extract_boolean(component, precision_timestamp): -> boolean
2. extract_boolean(component, timestamp_tz, string): -> boolean
3. extract_boolean(component, precision_timestamp_tz, string): -> boolean
4. extract_boolean(component, date): -> boolean

*Extract boolean values of a date/time value. * IS_LEAP_YEAR Return true if year of the given value is a leap year and false otherwise. * IS_DST Return true if DST (Daylight Savings Time) is observed at the given value in the given timezone.

Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.*

Options:

component ['IS_LEAP_YEAR', 'IS_DST']

component ['IS_LEAP_YEAR']

add¶

Implementations:
add(x, y): -> return_type
0. add(timestamp, interval_year): -> timestamp
1. add(precision_timestamp, interval_year): -> precision_timestamp
2. add(timestamp_tz, interval_year, string): -> timestamp_tz
3. add(precision_timestamp_tz, interval_year, string): -> precision_timestamp_tz
4. add(date, interval_year): -> timestamp
5. add(timestamp, interval_day): -> timestamp
6. add(precision_timestamp, interval_day): -> precision_timestamp
7. add(timestamp_tz, interval_day): -> timestamp_tz
8. add(precision_timestamp_tz, interval_day): -> precision_timestamp_tz
9. add(date, interval_day): -> timestamp

Add an interval to a date/time type. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

multiply¶

Implementations:
multiply(x, y): -> return_type
0. multiply(i8, interval_day): -> interval_day
1. multiply(i16, interval_day): -> interval_day
2. multiply(i32, interval_day): -> interval_day
3. multiply(i64, interval_day): -> interval_day
4. multiply(i8, interval_year): -> interval_year
5. multiply(i16, interval_year): -> interval_year
6. multiply(i32, interval_year): -> interval_year
7. multiply(i64, interval_year): -> interval_year

Multiply an interval by an integral number.

add_intervals¶

Implementations:
add_intervals(x, y): -> return_type
0. add_intervals(interval_day, interval_day): -> interval_day
1. add_intervals(interval_year, interval_year): -> interval_year

Add two intervals together.

subtract¶

Implementations:
subtract(x, y): -> return_type
0. subtract(timestamp, interval_year): -> timestamp
1. subtract(precision_timestamp, interval_year): -> precision_timestamp
2. subtract(timestamp_tz, interval_year): -> timestamp_tz
3. subtract(precision_timestamp_tz, interval_year): -> precision_timestamp_tz
4. subtract(timestamp_tz, interval_year, string): -> timestamp_tz
5. subtract(precision_timestamp_tz, interval_year, string): -> precision_timestamp_tz
6. subtract(date, interval_year): -> date
7. subtract(timestamp, interval_day): -> timestamp
8. subtract(precision_timestamp, interval_day): -> precision_timestamp
9. subtract(timestamp_tz, interval_day): -> timestamp_tz
10. subtract(precision_timestamp_tz, interval_day): -> precision_timestamp_tz
11. subtract(date, interval_day): -> date

Subtract an interval from a date/time type. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

lte¶

Implementations:
lte(x, y): -> return_type
0. lte(timestamp, timestamp): -> boolean
1. lte(precision_timestamp, precision_timestamp): -> boolean
2. lte(timestamp_tz, timestamp_tz): -> boolean
3. lte(precision_timestamp_tz, precision_timestamp_tz): -> boolean
4. lte(date, date): -> boolean
5. lte(interval_day, interval_day): -> boolean
6. lte(interval_year, interval_year): -> boolean

less than or equal to

lt¶

Implementations:
lt(x, y): -> return_type
0. lt(timestamp, timestamp): -> boolean
1. lt(precision_timestamp, precision_timestamp): -> boolean
2. lt(timestamp_tz, timestamp_tz): -> boolean
3. lt(precision_timestamp_tz, precision_timestamp_tz): -> boolean
4. lt(date, date): -> boolean
5. lt(interval_day, interval_day): -> boolean
6. lt(interval_year, interval_year): -> boolean

less than

gte¶

Implementations:
gte(x, y): -> return_type
0. gte(timestamp, timestamp): -> boolean
1. gte(precision_timestamp, precision_timestamp): -> boolean
2. gte(timestamp_tz, timestamp_tz): -> boolean
3. gte(precision_timestamp_tz, precision_timestamp_tz): -> boolean
4. gte(date, date): -> boolean
5. gte(interval_day, interval_day): -> boolean
6. gte(interval_year, interval_year): -> boolean

greater than or equal to

gt¶

Implementations:
gt(x, y): -> return_type
0. gt(timestamp, timestamp): -> boolean
1. gt(precision_timestamp, precision_timestamp): -> boolean
2. gt(timestamp_tz, timestamp_tz): -> boolean
3. gt(precision_timestamp_tz, precision_timestamp_tz): -> boolean
4. gt(date, date): -> boolean
5. gt(interval_day, interval_day): -> boolean
6. gt(interval_year, interval_year): -> boolean

greater than

assume_timezone¶

Implementations:
assume_timezone(x, timezone): -> return_type

x: Timezone string from IANA tzdb.

0. assume_timezone(timestamp, string): -> timestamp_tz
1. assume_timezone(precision_timestamp, string): -> precision_timestamp_tz
2. assume_timezone(date, string): -> timestamp_tz

Convert local timestamp to UTC-relative timestamp_tz using given local time’s timezone. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

local_timestamp¶

Implementations:
local_timestamp(x, timezone): -> return_type

x: Timezone string from IANA tzdb.

0. local_timestamp(timestamp_tz, string): -> timestamp
1. local_timestamp(precision_timestamp_tz, string): -> precision_timestamp

Convert UTC-relative timestamp_tz to local timestamp using given local time’s timezone. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

strptime_time¶

Implementations:
strptime_time(time_string, format): -> return_type
0. strptime_time(string, string): -> time

Parse string into time using provided format, see https://man7.org/linux/man-pages/man3/strptime.3.html for reference.

strptime_date¶

Implementations:
strptime_date(date_string, format): -> return_type
0. strptime_date(string, string): -> date

Parse string into date using provided format, see https://man7.org/linux/man-pages/man3/strptime.3.html for reference.

strptime_timestamp¶

Implementations:
strptime_timestamp(timestamp_string, format, timezone): -> return_type

timestamp_string: Timezone string from IANA tzdb.

0. strptime_timestamp(string, string, string): -> timestamp_tz
1. strptime_timestamp(string, string): -> timestamp_tz

Parse string into timestamp using provided format, see https://man7.org/linux/man-pages/man3/strptime.3.html for reference. If timezone is present in timestamp and provided as parameter an error is thrown. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is supplied as parameter and present in the parsed string the parsed timezone is used. If parameter supplied timezone is invalid an error is thrown.

strftime¶

Implementations:
strftime(x, format): -> return_type
0. strftime(timestamp, string): -> string
1. strftime(precision_timestamp, string): -> string
2. strftime(timestamp_tz, string, string): -> string
3. strftime(precision_timestamp_tz, string, string): -> string
4. strftime(date, string): -> string
5. strftime(time, string): -> string

Convert timestamp/date/time to string using provided format, see https://man7.org/linux/man-pages/man3/strftime.3.html for reference. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

round_temporal¶

Implementations:
round_temporal(x, rounding, unit, multiple, origin): -> return_type
0. round_temporal(timestamp, rounding, unit, i64, timestamp): -> timestamp
1. round_temporal(precision_timestamp, rounding, unit, i64, precision_timestamp): -> precision_timestamp
2. round_temporal(timestamp_tz, rounding, unit, i64, string, timestamp_tz): -> timestamp_tz
3. round_temporal(precision_timestamp_tz, rounding, unit, i64, string, precision_timestamp_tz): -> precision_timestamp_tz
4. round_temporal(date, rounding, unit, i64, date): -> date
5. round_temporal(time, rounding, unit, i64, time): -> time

Round a given timestamp/date/time to a multiple of a time unit. If the given timestamp is not already an exact multiple from the origin in the given timezone, the resulting point is chosen as one of the two nearest multiples. Which of these is chosen is governed by rounding: FLOOR means to use the earlier one, CEIL means to use the later one, ROUND_TIE_DOWN means to choose the nearest and tie to the earlier one if equidistant, ROUND_TIE_UP means to choose the nearest and tie to the later one if equidistant. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

Options:

rounding ['FLOOR', 'CEIL', 'ROUND_TIE_DOWN', 'ROUND_TIE_UP']

unit ['HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND']

unit ['YEAR', 'MONTH', 'WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND']

unit ['YEAR', 'MONTH', 'WEEK', 'DAY']

round_calendar¶

Implementations:
round_calendar(x, rounding, unit, origin, multiple): -> return_type
0. round_calendar(timestamp, rounding, unit, origin, i64): -> timestamp
1. round_calendar(precision_timestamp, rounding, unit, origin, i64): -> precision_timestamp
2. round_calendar(timestamp_tz, rounding, unit, origin, i64, string): -> timestamp_tz
3. round_calendar(precision_timestamp_tz, rounding, unit, origin, i64, string): -> precision_timestamp_tz
4. round_calendar(date, rounding, unit, origin, i64, date): -> date
5. round_calendar(time, rounding, unit, origin, i64, time): -> time

Round a given timestamp/date/time to a multiple of a time unit. If the given timestamp is not already an exact multiple from the last origin unit in the given timezone, the resulting point is chosen as one of the two nearest multiples. Which of these is chosen is governed by rounding: FLOOR means to use the earlier one, CEIL means to use the later one, ROUND_TIE_DOWN means to choose the nearest and tie to the earlier one if equidistant, ROUND_TIE_UP means to choose the nearest and tie to the later one if equidistant. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: “Pacific/Marquesas”, “Etc/GMT+1”. If timezone is invalid an error is thrown.

Options:

origin ['DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND']

origin ['YEAR', 'MONTH', 'MONDAY_WEEK', 'SUNDAY_WEEK', 'ISO_WEEK', 'US_WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND']

origin ['YEAR', 'MONTH', 'MONDAY_WEEK', 'SUNDAY_WEEK', 'ISO_WEEK', 'US_WEEK', 'DAY']

rounding ['FLOOR', 'CEIL', 'ROUND_TIE_DOWN', 'ROUND_TIE_UP']

unit ['DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND']

unit ['YEAR', 'MONTH', 'WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND']

unit ['YEAR', 'MONTH', 'WEEK', 'DAY']

Aggregate Functions¶

min¶

Implementations:
min(x): -> return_type
0. min(date): -> date?
1. min(time): -> time?
2. min(timestamp): -> timestamp?
3. min(precision_timestamp): -> precision_timestamp?
4. min(timestamp_tz): -> timestamp_tz?
5. min(precision_timestamp_tz): -> precision_timestamp_tz?
6. min(interval_day): -> interval_day?
7. min(interval_year): -> interval_year?

Min a set of values.

max¶

Implementations:
max(x): -> return_type
0. max(date): -> date?
1. max(time): -> time?
2. max(timestamp): -> timestamp?
3. max(precision_timestamp): -> precision_timestamp?
4. max(timestamp_tz): -> timestamp_tz?
5. max(precision_timestamp_tz): -> precision_timestamp_tz?
6. max(interval_day): -> interval_day?
7. max(interval_year): -> interval_year?

Max a set of values.