Date and Time Functions

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_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

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 and DATE. 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 (SunSat)

%b

Abbreviated month name (JanDec)

%c

Month, numeric (112) [4]

%D

Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)

%d

Day of the month, numeric (0131) [4]

%e

Day of the month, numeric (131) [4]

%f

Fraction of second (6 digits for printing: 000000999000; 1 - 9 digits for parsing: 0999999999) [1]

%H

Hour (0023)

%h

Hour (0112)

%I

Hour (0112)

%i

Minutes, numeric (0059)

%j

Day of year (001366)

%k

Hour (023)

%l

Hour (112)

%M

Month name (JanuaryDecember)

%m

Month, numeric (0112) [4]

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (0059)

%s

Seconds (0059)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (0053), where Sunday is the first day of the week

%u

Week (0053), where Monday is the first day of the week

%V

Week (0153), where Sunday is the first day of the week; used with %X

%v

Week (0153), where Monday is the first day of the week; used with %x

%W

Weekday name (SundaySaturday)

%w

Day of the week (06), 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

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, 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.

day(x) bigint

Returns the day of the month from x.

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.

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.

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().