Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

Date and time functions

Prev Next

Applies to VAST Cluster 5.5.

Parts of dates and times, truncating and bucketing, date arithmetic and intervals, building, parsing and formatting values, and time zones.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.

Parts of a date or time

Function What it does
date_part (also datepart, extract(part FROM value), date_part('part', value)) Take a named part, such as the year, the hour or the epoch, out of a DATE, TIMESTAMP, TIME or INTERVAL; datepart and extract are the same function.
day (also dayofmonth, date_part('day', value), extract(day FROM value)) Get the day of the month (1 to 31) of a DATE or TIMESTAMP, or the days field of an INTERVAL; dayofmonth is the same function.
hour Get the hour (0 to 23) of a TIMESTAMP or TIME, or the hours of an INTERVAL.
minute Get the minute (0 to 59) of a TIMESTAMP or TIME, or the minutes of an INTERVAL.
month Get the month (1 to 12) of a DATE or TIMESTAMP, or the months field of an INTERVAL.
quarter Get the quarter of the year (1 to 4) of a DATE or TIMESTAMP, or of an INTERVAL's months.
second Get the whole second (0 to 59) of a TIMESTAMP or TIME, or the seconds of an INTERVAL.
year Get the calendar year of a DATE or TIMESTAMP, or the years field of an INTERVAL.
century Get the century of a DATE or TIMESTAMP (2001 to 2100 is the 21st), or the centuries of an INTERVAL.
decade Get the decade of a DATE or TIMESTAMP (the year divided by 10), or of the years of an INTERVAL.
era Tell AD from BC dates: era returns 1 for a DATE or TIMESTAMP in year 1 or later and 0 before it.
microsecond Get the seconds of a TIMESTAMP, TIME or INTERVAL with their fraction, in microseconds (0 to 59999999).
millennium Get the millennium of a DATE or TIMESTAMP (2001 to 3000 is the 3rd), or the millennia of an INTERVAL.
millisecond Get the seconds of a TIMESTAMP, TIME or INTERVAL with their fraction, in milliseconds (0 to 59999).
nanosecond Get the seconds of a TIMESTAMP_NS, TIMESTAMP, TIME or INTERVAL with their fraction, in nanoseconds.

Weeks and days of the week

Function What it does
week (also weekofyear) Get the ISO 8601 week number (1 to 53) of a DATE or TIMESTAMP; weekofyear is the same function.
dayofweek (also weekday) Get the day of the week of a DATE or TIMESTAMP as a number from 0 (Sunday) to 6 (Saturday); weekday is the same function.
dayofyear Get the day of the year of a DATE or TIMESTAMP, from 1 for 1 January to 365 or 366.
isodow Get the ISO 8601 day of the week of a DATE or TIMESTAMP as a number from 1 (Monday) to 7 (Sunday).
isoyear Get the ISO 8601 week-numbering year of a DATE or TIMESTAMP, the year its ISO week belongs to.
yearweek Get the ISO 8601 year and week of a DATE or TIMESTAMP as one number, YYYYWW.

Names and calendar

Function What it does
dayname Get the English name of the day of the week of a DATE or TIMESTAMP, such as Sunday.
last_day Get the last day of the month of a DATE or TIMESTAMP, as a DATE.
monthname Get the English name of the month of a DATE or TIMESTAMP, such as March.
julian Get the Julian day number of a DATE or TIMESTAMP, with the time of day as a fraction.

Truncating and bucketing

Function What it does
date_trunc (also datetrunc) Round a DATE, TIMESTAMP or INTERVAL down to the start of its year, quarter, month, week, day, hour, minute or second; datetrunc is the same function.
time_bucket Put a DATE or TIMESTAMP in a bucket of a fixed width, such as six hours or seven days, and return the bucket's start.

Epoch values

Function What it does
epoch_ms Get the whole milliseconds since 1970-01-01 of a DATE or TIMESTAMP, or turn a count of milliseconds into a TIMESTAMP.
epoch_ns Get the whole nanoseconds since 1970-01-01 of a DATE, TIMESTAMP or TIMESTAMP_NS, of a TIME since midnight, or of an INTERVAL.
epoch_us Get the whole microseconds since 1970-01-01 of a DATE or TIMESTAMP, of a TIME since midnight, or of an INTERVAL.
epoch Get the seconds since 1970-01-01 of a DATE or TIMESTAMP, the seconds since midnight of a TIME, or the length of an INTERVAL in seconds.

Special values

Function What it does
isfinite Test whether a DATE or TIMESTAMP is an ordinary value rather than 'infinity' or '-infinity'.
isinf Test whether a DATE or TIMESTAMP is one of the special values 'infinity' or '-infinity'.

Differences

Function What it does
age Get the time between two timestamps as an INTERVAL in months, days and time, or the age of one timestamp at the start of today.
date_diff (also datediff) Count the part boundaries, such as month or day boundaries, crossed between two dates, timestamps or times; datediff is the same function.
date_sub (also datesub) Count the complete parts, such as whole months or whole days, between two dates, timestamps or times; datesub is the same function.

Adding and interval arithmetic

Function What it does
date_add Add an INTERVAL to a DATE, TIMESTAMP or TIME, or a number of days to a DATE; date_add(a, b) is the same as a + b.
- (subtract a date or interval) (also subtract(a, b), subtract(interval)) Subtract an INTERVAL or a number of days from a date, timestamp or time, get the days or the interval between two values, or negate an interval, with the - operator; subtract() is the same function.
* (multiply an interval) (also multiply(a, b)) Multiply an INTERVAL by a whole number with the * operator, for example to step a timestamp by n hours; multiply() is the same function.
+ (add a date or interval) (also add(a, b)) Add an INTERVAL to a DATE, TIMESTAMP or TIME, days to a DATE, a TIME to a DATE, or two intervals, with the + operator; add() is the same function.
/ (divide an interval) Divide an INTERVAL by a whole number: each field is divided separately and the remainder is dropped.
normalized_interval Carry the time of an INTERVAL into days and its days into months (24 hours to a day, 30 days to a month).

Building values

Function What it does
make_date Build a DATE from a year, month and day, from a struct of them, or from a count of days since 1970-01-01.
make_time Build a TIME of day from an hour, a minute and a number of seconds with a fraction.
make_timestamp_ns Build a TIMESTAMP_NS from a number of nanoseconds since 1970-01-01, and read its nanoseconds back.
make_timestamp Build a TIMESTAMP from its year, month, day, hour, minute and seconds, or from microseconds since 1970-01-01.
to_timestamp Turn a number of seconds since 1970-01-01 (Unix time) into a TIMESTAMP WITH TIME ZONE in UTC.

Interval constructors

Function What it does
to_years Build an INTERVAL of a whole number of years (stored as 12 months each) from an INTEGER.
to_days Build an INTERVAL of n days from an INTEGER, the same as INTERVAL n DAY.
to_hours Build an INTERVAL of n hours from a BIGINT, the same as INTERVAL n HOUR.
to_milliseconds Build an INTERVAL of n milliseconds, with a fraction if you need one, the same as INTERVAL n MILLISECOND.
to_minutes Build an INTERVAL of n minutes from a BIGINT, the same as INTERVAL n MINUTE.
to_months Build an INTERVAL of n months from an INTEGER, the same as INTERVAL n MONTH.
to_quarters Build an INTERVAL of n quarters from an INTEGER, the same as INTERVAL n QUARTER.
to_seconds Build an INTERVAL of n seconds, with a fraction if you need one, the same as INTERVAL n SECOND.
to_weeks Build an INTERVAL of n weeks from an INTEGER, the same as INTERVAL n WEEK.
to_centuries Build an INTERVAL of n centuries (1200 months each) from an INTEGER.
to_decades Build an INTERVAL of n decades (120 months each) from an INTEGER.
to_microseconds Build an INTERVAL of n microseconds from a BIGINT, the same as INTERVAL n MICROSECOND.
to_millennia Build an INTERVAL of n millennia (12000 months each) from an INTEGER.

The current time

Function What it does
now (also current_timestamp, get_current_timestamp, transaction_timestamp) Get the current date and time as a TIMESTAMP WITH TIME ZONE in UTC; current_timestamp, get_current_timestamp and transaction_timestamp are the same function.

Parsing and formatting

Function What it does
strftime Format a DATE or TIMESTAMP as text with % codes such as '%d/%m/%Y %H:%M'.
strptime Parse text into a TIMESTAMP with a format such as '%d/%m/%Y', or a list of formats to try in turn.
try_strptime Parse text into a TIMESTAMP with a format, or a list of formats, returning NULL for text that doesn't match.

Time zones

Function What it does
Time zones What the VAST Query Engine does with time zones: values with a UTC offset are converted to UTC, and named zones, time-zone conversion, current_date and the timezone functions aren't available.

See also

Related articles