Overview

add_months

add_months(string start_date, int num_months)

STRING

Returns the date that is num_months after start_date.

current_date

current_date()

DATE

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

current_timestamp

current_timestamp()

TIMESTAMP

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

date_add

date_add(string startdate, int days)

STRING or DATE

Adds a number of days to a date.

dateadd

dateadd(string date, bigint delta, string datepart)

STRING or DATE

Changes a date based on datepart and delta.

date: This parameter is mandatory. Date value, which is of the STRING type.

The time format is yyyy-mm-dd hh:mi:ss, for example, 2021-08-28 00:00:00.

delta: This parameter is mandatory. Adjustment amplitude, which is of the BIGINT type.

datepart: Adjustment unit, which is a constant of the STRING type. This parameter is mandatory.

date_sub

date_sub(string startdate, int days)

STRING

Subtracts a number of days from a date.

date_format

date_format(string date, string format)

STRING

Converts a date into a string based on the format specified by format.

datediff

datediff(string date1, string date2)

BIGINT

Calculates the difference between date1 and date2.

datediff1

datediff1(string date1, string date2, string datepart)

BIGINT

Calculates the difference between date1 and date2 and returns the difference in a specified datepart.

datepart

datepart (string date, string datepart)

BIGINT

Returns the value of the field that meets a specified time unit in the date.

datetrunc

datetrunc (string date, string datepart)

STRING

Calculates the date otained through the truncation of a specified date based on a specified datepart.

date: The value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. This parameter is mandatory.

datepart: Supported date format, which is a STRING constant. This parameter is mandatory.

day/dayofmonth

day(string date), dayofmonth(string date)

INT

Returns the date of a specified time.

from_unixtime

from_unixtime(bigint unixtime)

STRING

Converts a timestamp to a time, in yyyy-MM-dd HH:mm:ss or yyyyMMddHHmmss.uuuuuu format.

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

from_utc_timestamp

from_utc_timestamp(string timestamp, string timezone)

TIMESTAMP

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

getdate

getdate()

STRING

Obtains the current system time.

hour

hour(string date)

INT

Returns the hour (from 0 to 23) of a specified time.

isdate

isdate(string date , string format)

BOOLEAN

date: Date, which is implicitly converted to the STRING type and then used for calculation. This parameter is mandatory.

format: Unsupported date extension format, which is a STRING constant. This parameter is mandatory. If there are redundant format strings in format, only the date value corresponding to the first format string is used. Other format strings are used as separators. For example, isdate("1234-yyyy", "yyyy-yyyy") returns True.

last_day

last_day(string date)

DATE

Returns the last day of the month a date belongs to, in the format of yyyy-MM-dd, for example, 2015-08-31.

lastday

lastday(string date)

STRING

Returns the last day of the month a date belongs to. The value is of the STRING type and is in the yyyy-mm-dd hh:mi:ss format.

minute

minute(string date)

INT

Returns the minute (from 0 to 59) of a specified time.

month

month(string date)

INT

Returns the month (from January to December) of a specified time.

months_between

months_between(string date1, string date2)

DOUBLE

Returns the month difference between date1 and date2.

next_day

next_day(string start_date, string day_of_week)

DATE

Returns the date closest to day_of_week after start_date, in the yyyy-MM-dd format. day_of_week indicates a day in a week, for example, Monday or Friday.

quarter

quarter(string date)

INT

Returns the quarter of the date, timestamp, or string, for example, quarter('2015-04-01')=2.

second

second(string date)

INT

Returns the second (from 0 to 59) of a specified time.

to_char

to_char(string date, string format)

STRING

Converts a date into a string in a specified format.

to_date

to_date(string timestamp)

DATE

Returns the date part of a time string, for example, to_date("1970-01-01 00:00:00") = "1970-01-01".

to_date1

to_date1(string date, string format)

STRING

The value is of the STRING type, in the yyyy-mm-dd hh:mi:ss format. If the value of date or format is NULL, NULL is returned.

Converts a string in a specified format to a date value.

to_utc_timestamp

to_utc_timestamp(string timestamp, string timezone)

TIMESTAMP

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

trunc

trunc(string date, string format)

DTAE

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

unix_timestamp

unix_timestamp(string timestamp, string pattern)

BIGINT

Returns a Unix timestamp (the number of seconds since 1970-01-01 00:00:00) as an unsigned integer if the function is called without parameters.

weekday

weekday (string date)

INT

Returns the day of the current week.

weekofyear

weekofyear(string date)

INT

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

year

year(string date)

INT

Returns the year of a specified date.