Date Functions

from_unixtime(bigint unixtime[, string format])

STRING

Convert a timestamp to the time format "yyyy-MM-dd HH:mm:ss" or "yyyyMMddHHmmss.uuuuuu".

For example, select FROM_UNIXTIME(1608135036,'yyyy-MM-dd HH:mm:ss').

unix_timestamp()

BIGINT

Return a Unix timestamp (the number of seconds that have elapsed since 1970-01-01 00:00:00) represented by an unsigned integer when the function is called without arguments.

unix_timestamp(string date)

BIGINT

Return the number of seconds between a specified date and 1970-01-01 00:00:00.

unix_timestamp(string date, string pattern)

BIGINT

Convert a time string with a given pattern to a Unix timestamp. Example: unix_timestamp("2009-03-20", "yyyy-MM-dd") = 1237532400.

to_date(string timestamp)

STRING

Return the date part of a time string. Example: to_date("1970-01-01 00:00:00") = "1970-01-01".

year(string date)

INT

Return the year part of a date.

quarter(string date/timestamp/string)

INT

Return the quarter of the year for a date, timestamp, or string. Example: quarter('2015-04-01')=2.

month(string date)

INT

Return the month (from 1 to 12) part of a date.

day(string date) dayofmonth(string date)

INT

Return the day part of a date.

hour(string date)

INT

Return the hour (from 0 to 23) part of a date.

minute(string date)

INT

Return the minute (from 0 to 59) part of a date.

second(string date)

INT

Return the second (from 0 to 59) part of a date.

weekofyear(string date)

INT

Return the week number (from 0 to 53) of a date.

datediff(string enddate, string startdate)

INT

Return the number of days from startdate to enddate.

date_add(string startdate, int days)

STRING

Add a number of days to a date.

date_sub(string startdate, int days)

STRING

Subtract a number of days from a date.

from_utc_timestamp(string timestamp, string timezone)

TIMESTAMP

Convert a UTC timestamp to a timestamp in a given time zone. For example, from_utc_timestamp('1970-01-01 08:00:00','PST') returns 1970-01-01 00:00:00.

to_utc_timestamp(string timestamp, string timezone)

TIMESTAMP

Convert a timestamp in a given time zone to a UTC timestamp. For example, to_utc_timestamp('1970-01-01 00:00:00','PST') returns 1970-01-01 08:00:00.

current_date()

DATE

Return the current date, for example, 2016-07-04.

current_timestamp()

TIMESTAMP

Return the current time, for example, 2016-07-04 11:18:11.685.

add_months(string start_date, int num_months)

STRING

Return the date that is num_months after start_date.

last_day(string date)

STRING

Return the last day of the month to which a date belongs. The returned date is in the format of yyyy-MM-dd, for example, 2015-08-31.

next_day(string start_date, string day_of_week)

STRING

Return the first date that is later than start_date and nearest to day_of_week. The returned date in the format of yyyy-MM-dd. day_of_week specifies a day of a week. For example, the value of day_of_week can be Monday or FRIDAY.

trunc(string date, string format)

STRING

Reset the date in a specified format. Supported formats are MONTH/MON/MM and YEAR/YYYY/YY. Example: trunc('2015-03-17', 'MM') = 2015-03-01.

months_between(string date1, string date2)

DOUBLE

Return number of months between dates date1 and date2.

date_format(date/timestamp/string ts, string fmt)

STRING

Return the formatted value of date/timestamp/string. The Java SimpleDateFormat format is supported. Example: date_format('2015-04-08', 'y') = '2015'.

In the format, y indicates the year. Y indicates the year when the current week is located. A week starts from Sunday and ends on Saturday. If a week crosses years, this week is counted as the next year.