Date and Time Functions and Operators

Date and Time Operators

Operator	Example	Result
+	interval '1' second + interval '1' hour	0 01:00:01.000
+	timestamp '1970-01-01 00:00:00.000' + interval '1' second	1970-01-01 00:00:01.000
-	interval '1' hour - interval '1' second	0 00:59:59.000
-	timestamp '1970-01-01 00:00:00.000' - interval '1' second	1969-12-31 23:59:59.000
*	interval '1' second * 2	0 00:00:02.000
*	2 * interval '1' second	0 00:00:02.000
*	interval '1' second * 0.001	0 00:00:00.001
*	0.001 * interval '1' second	0 00:00:00.001
/	interval '15' second / 1.5	0 00:00:10.000

plus(x, y) → [same as x]

Returns the sum of x and y. Both x and y are intervals day to second or one of them can be timestamp. For addition of two intervals day to second, returns -106751991167 07:12:55.808 when the addition overflows in positive and returns 106751991167 07:12:55.807 when the addition overflows in negative. When addition of a timestamp with an interval day to second, overflowed results are wrapped around.

minus(x, y) → [same as x]

Returns the result of subtracting y from x. Both x and y are intervals day to second or x can be timestamp. For subtraction of two intervals day to second, returns -106751991167 07:12:55.808 when the subtraction overflows in positive and returns 106751991167 07:12:55.807 when the subtraction overflows in negative. For subtraction of an interval day to second from a timestamp, overflowed results are wrapped around.

multiply(interval day to second, x) → interval day to second

Returns the result of multiplying interval day to second by x. x can be a bigint or double. Returns 0 when x is NaN. Returns 106751991167 07:12:55.807 when x is infinity or when the multiplication overflow in positive. Returns -106751991167 07:12:55.808 when x is -infinity or when the multiplication overflow in negiative.

multiply(x, interval day to second) → interval day to second

Returns the result of multiplying x by interval day to second. Same as multiply(interval day to second, x).

divide(interval day to second, x) → interval day to second

Returns the result of interval day to second divided by x. x is a double. Returns 0 when x is NaN or is infinity. Returns 106751991167 07:12:55.807 when x is 0.0 and interval day to second is not 0, or when the division overflows in positive. Returns -106751991167 07:12:55.808 when x is -0.0 and interval day to second is not 0, or when the division overflows in negiative.

Date and Time Functions

current_date() → date

Returns the current date.

date(x) → date

This is an alias for CAST(x AS date).

from_iso8601_date(string) → date

Parses the ISO 8601 formatted string into a date. ISO 8601 string can be formatted as any of the following: [+-][Y]Y*

[+-][Y]Y*-[M]M*

[+-][Y]Y*-[M]M*-[D]D*

[+-][Y]Y*-[M]M*-[D]D* *

Year value must contain at least one digit, and may contain up to six digits. Month and day values are optional and may each contain one or two digits.

Examples of supported input strings: “2012”, “2012-4”, “2012-04”, “2012-4-7”, “2012-04-07”, “2012-04-07 ”

from_unixtime(unixtime) → timestamp

Returns the UNIX timestamp unixtime as a timestamp.

from_unixtime(unixtime, string) → timestamp with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zone using string for the time zone.

to_iso8601(x) → varchar

Formats x as an ISO 8601 string. Supported types for x are: DATE.

to_unixtime(timestamp) → double

Returns timestamp as a UNIX timestamp.

Truncation Function

The date_trunc function supports the following units:

Unit	Example Truncated Value
second	2001-08-22 03:04:05.000
minute	2001-08-22 03:04:00.000
hour	2001-08-22 03:00:00.000
day	2001-08-22 00:00:00.000
week	2001-08-20 00:00:00.000
month	2001-08-01 00:00:00.000
quarter	2001-07-01 00:00:00.000
year	2001-01-01 00:00:00.000

The above examples use the timestamp 2001-08-22 03:04:05.321 as the input.

date_trunc(unit, x) → x

Returns x truncated to unit. The supported types for x are TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE.

Interval Functions

The functions in this section support the following interval units:

Unit	Description
millisecond	Milliseconds
second	Seconds
minute	Minutes
hour	Hours
day	Days
month	Months
quarter	Quarters of a year
year	Years

date_add(unit, value, x) → x

Adds an interval value of type unit to x. The supported types for x are TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE. Subtraction can be performed by using a negative value.

date_diff(unit, x1, x2) → bigint

Returns x2 - x1 in terms of unit. The supported types for x are TIMESTAMP and DATE.

MySQL Date Functions

The functions in this section use a format string that is compatible with the MySQL date_parse and str_to_date functions. The following table, based on the MySQL manual, describes the format specifiers:

Specifier	Description
%a	Abbreviated weekday name (Sun … Sat)
%b	Abbreviated month name (Jan … Dec)
%c	Month, numeric (1 … 12) [4]
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (01 … 31) [4]
%e	Day of the month, numeric (1 … 31) [4]
%f	Fraction of second (6 digits for printing: 000000 … 999000; 1 - 9 digits for parsing: 0 … 999999999) [1]
%H	Hour (00 … 23)
%h	Hour (01 … 12)
%I	Hour (01 … 12)
%i	Minutes, numeric (00 … 59)
%j	Day of year (001 … 366)
%k	Hour (0 … 23)
%l	Hour (1 … 12)
%M	Month name (January … December)
%m	Month, numeric (01 … 12) [4]
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00 … 59)
%s	Seconds (00 … 59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00 … 53), where Sunday is the first day of the week
%u	Week (00 … 53), where Monday is the first day of the week
%V	Week (01 … 53), where Sunday is the first day of the week; used with %X
%v	Week (01 … 53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday … Saturday)
%w	Day of the week (0 … 6), where Sunday is the first day of the week [3]
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits) [2]
%%	A literal % character
%x	x, for any x not listed above

[1]

Timestamp is truncated to milliseconds.

[2]

When parsing, two-digit year format assumes range 1970 … 2069, so “70” will result in year 1970 but “69” will produce 2069.

[3]

This specifier is not supported yet. Consider using day_of_week() (it uses 1-7 instead of 0-6).

[4] (1,2,3,4)

This specifier does not support 0 as a month or day.

Warning: The following specifiers are not currently supported: %D, %U, %u, %V, %w, %X.

date_format(x, format) → varchar

Formats x as a string using format. x is a timestamp or a timestamp with time zone.

Java Date Functions

The functions in this section leverage a native cpp implementation that follows a format string compatible with JodaTime’s DateTimeFormat pattern format. The symbols currently supported are y, Y, M , d, H, m, s, S, z and Z.

z represents a timezone name (3-letter format), and Z a timezone offset specified using the format +00, +00:00 or +0000 (or -). Z also accepts UTC, UCT, GMT, and GMT0 as valid representations of GMT.

parse_datetime(string, format) → timestamp with time zone

Parses string into a timestamp with time zone using format.

Convenience Extraction Functions

These functions support TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE input types.

For these functions, the input timestamp has range limitations on seconds and nanoseconds. Seconds should be in the range [INT64_MIN/1000 - 1, INT64_MAX/1000], nanoseconds should be in the range [0, 999999999]. This behavior is different from Presto Java that allows arbitrary large timestamps.

day(x) → bigint

Returns the day of the month from x.

The supported types for x are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND.

day_of_month(x) → bigint

This is an alias for day().

day_of_week(x) → bigint

Returns the ISO day of the week from x. The value ranges from 1 (Monday) to 7 (Sunday).

day_of_year(x) → bigint

Returns the day of the year from x. The value ranges from 1 to 366.

dow(x) → bigint

This is an alias for day_of_week().

doy(x) → bigint

This is an alias for day_of_year().

hour(x) → bigint

Returns the hour of the day from x. The value ranges from 0 to 23.

last_day_of_month(x) → date

Returns the last day of the month.

millisecond(x) → int64

Returns the millisecond of the second from x.

minute(x) → bigint

Returns the minute of the hour from x.

month(x) → bigint

Returns the month of the year from x.

quarter(x) → bigint

Returns the quarter of the year from x. The value ranges from 1 to 4.

second(x) → bigint

Returns the second of the minute from x.

timezone_hour(timestamp) → bigint

Returns the hour of the time zone offset from timestamp.

timezone_minute(timestamp) → bigint

Returns the minute of the time zone offset from timestamp.

week(x) → bigint

Returns the ISO-Week of the year from x. The value ranges from 1 to 53.

week_of_year(x) → bigint

This is an alias for week().

year(x) → bigint

Returns the year from x.

year_of_week(x) → bigint

Returns the year of the ISO week from x.

yow(x) → bigint

This is an alias for year_of_week().

Time Zones

Velox has full support for time zone rules, which are needed to perform date/time calculations correctly. Typically, the session time zone is used for temporal calculations. This is the time zone of the client computer that submits the query, if available. Otherwise, it is the time zone of the server running the Presto coordinator.

Queries that operate with time zones that follow daylight saving can produce unexpected results. For example, if we run the following query in the America/Los Angeles time zone:

SELECT date_add('hour', 24, cast('2014-03-08 09:00:00' as timestamp));
-- 2014-03-09 10:00:00.000

The timestamp appears to only advance 23 hours. This is because on March 9th clocks in America/Los Angeles are turned forward 1 hour, so March 9th only has 23 hours. To advance the day part of the timestamp, use the day unit instead:

SELECT date_add('day', 1, cast('2014-03-08 09:00:00' as timestamp));
-- 2014-03-09 09:00:00.000

This works because the date_add() function treats the timestamp as list of fields, adds the value to the specified field and then rolls any overflow into the next higher field.

Time zones are also necessary for parsing and printing timestamps. Queries that use this functionality can also produce unexpected results. For example, on the same machine:

SELECT cast('2014-03-09 02:30:00' as timestamp);

The above query causes an error because there was no 2:30 AM on March 9th in America/Los_Angeles due to a daylight saving time transition.

Similarly, the following query has two possible outcomes due to a daylight saving time transition:

SELECT cast('2014-11-02 01:30:00' as timestamp);
-- 2014-11-02 08:30:00.000

It can be interpreted as 2014-11-02 01:30:00 PDT, or 2014-11-02 01:30:00 PST, which are 2014-11-02 08:30:00 UTC or 2014-11-02 09:30:00 UTC respectively. The former one is picked to be consistent with Presto.

Timezone Name Parsing: When parsing strings that contain timezone names, the list of supported timezones follow the definition here.