Date and Time Functions and Operators¶
Date and Time Operators¶
Operator 
Example 
Result 




























 plus(x, y) [same as x] ¶
Returns the sum of
x
andy
. Bothx
andy
are intervals day to second or one of them can be timestamp. For addition of two intervals day to second, returns106751991167 07:12:55.808
when the addition overflows in positive and returns106751991167 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
fromx
. Bothx
andy
are intervals day to second orx
can be timestamp. For subtraction of two intervals day to second, returns106751991167 07:12:55.808
when the subtraction overflows in positive and returns106751991167 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
byx
.x
can be a bigint or double. Returns0
whenx
is NaN. Returns106751991167 07:12:55.807
whenx
is infinity or when the multiplication overflow in positive. Returns106751991167 07:12:55.808
whenx
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
byinterval day to second
. Same asmultiply(interval day to second, x)
.
 divide(interval day to second, x) interval day to second ¶
Returns the result of
interval day to second
divided byx
.x
is a double. Returns0
whenx
is NaN or is infinity. Returns106751991167 07:12:55.807
whenx
is0.0
andinterval day to second
is not0
, or when the division overflows in positive. Returns106751991167 07:12:55.808
whenx
is0.0
andinterval day to second
is not0
, 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_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 usingstring
for the time zone.
 to_unixtime(timestamp) double ¶
Returns
timestamp
as a UNIX timestamp.
Truncation Function¶
The date_trunc
function supports the following units:
Unit 
Example Truncated Value 

















The above examples use the timestamp 20010822 03:04:05.321
as the input.
 date_trunc(unit, x) x ¶
Returns
x
truncated tounit
. The supported types forx
are TIMESTAMP, DATE, and TIMESTAMP WITH TIME ZONE.
Interval Functions¶
The functions in this section support the following interval units:
Unit 
Description 

















 date_add(unit, value, x) x ¶
Adds an interval
value
of typeunit
tox
. The supported types forx
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 ofunit
. The supported types forx
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 


Abbreviated weekday name ( 

Abbreviated month name ( 

Month, numeric ( 

Day of the month with English suffix ( 

Day of the month, numeric ( 

Day of the month, numeric ( 

Fraction of second (6 digits for printing: 

Hour ( 

Hour ( 

Hour ( 

Minutes, numeric ( 

Day of year ( 

Hour ( 

Hour ( 

Month name ( 

Month, numeric ( 



Time, 12hour ( 

Seconds ( 

Seconds ( 

Time, 24hour ( 

Week ( 

Week ( 

Week ( 

Week ( 

Weekday name ( 

Day of the week ( 

Year for the week where Sunday is the first day of the week, numeric, four digits; used with 

Year for the week, where Monday is the first day of the week, numeric, four digits; used with 

Year, numeric, four digits 

Year, numeric (two digits) [2] 

A literal 


Warning: The following specifiers are not currently supported: %D
, %U
, %u
, %V
, %w
, %X
.
 date_format(x, format) varchar ¶
Formats
x
as a string usingformat
.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
, and Z
.
 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
.
 day_of_week(x) bigint ¶
Returns the ISO day of the week from
x
. The value ranges from1
(Monday) to7
(Sunday).
 day_of_year(x) bigint ¶
Returns the day of the year from
x
. The value ranges from1
to366
.
 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 from1
to4
.
 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_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('20140308 09:00:00' as timestamp));
 20140309 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('20140308 09:00:00' as timestamp));
 20140309 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('20140309 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('20141102 01:30:00' as timestamp);
 20141102 08:30:00.000
It can be interpreted as 20141102 01:30:00 PDT, or 20141102 01:30:00 PST, which are 20141102 08:30:00 UTC or 20141102 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.