Date and Time Functions

Convenience Extraction Functions

These functions support TIMESTAMP and DATE input types.

date_add(start_date, num_days) → date

Returns the date that is num_days after start_date. If num_days is a negative value then these amount of days will be deducted from start_date.

date_sub(start_date, num_days) → date

Returns the date that is num_days before start_date. According to the inputs, the returned date will wrap around between the minimum negative date and maximum positive date. date_sub(‘1969-12-31’, -2147483648) get 5881580-07-11, and date_sub(‘2023-07-10’, -2147483648) get -5877588-12-29.

num_days can be positive or negative.

dayofyear(date) → integer

Returns Returns the day of year of the date/timestamp.

SELECT dayofyear(‘2016-04-09’); – 100

dayofmonth(date) → integer

Returns the day of month of the date/timestamp.

SELECT dayofmonth(‘2009-07-30’); – 30

dayofweek(date/timestamp) → integer

Returns the day of the week for date/timestamp (1 = Sunday, 2 = Monday, …, 7 = Saturday). We can use dow as alias for

SELECT dayofweek(‘2009-07-30’); – 5 SELECT dayofweek(‘2023-08-22 11:23:00.100’); – 3

dow(x) → integer

This is an alias for day_of_week().

last_day(date) → date

Returns the last day of the month which the date belongs to.

make_date(year, month, day) → date

Returns the date from year, month and day fields. year, month and day must be INTEGER. Throws an error if inputs are not valid.

The valid inputs need to meet the following conditions, month need to be from 1 (January) to 12 (December). day need to be from 1 to 31, and matches the number of days in each month. days of year-month-day - 1970-01-01 need to be in the range of INTEGER type.

to_unix_timestamp(string) → integer

Alias for unix_timestamp(string) -> integer.

to_unix_timestamp(string, format) → integer

Alias for unix_timestamp(string, format) -> integer.

unix_timestamp() → integer

Returns the current UNIX timestamp in seconds.

unix_timestamp(string) → integer

Returns the UNIX timestamp of time specified by string. Assumes the format yyyy-MM-dd HH:mm:ss. Returns null if string does not match format.

unix_timestamp(string, format) → integer

Returns the UNIX timestamp of time specified by string using the format described in the format string. The format follows Spark’s Datetime patterns for formatting and parsing. Returns null if string does not match format or if format is invalid.

week_of_year(x) → integer

Returns the ISO-Week of the year from x. The value ranges from 1 to 53. A week is considered to start on a Monday and week 1 is the first week with >3 days.

year(x) → integer

Returns the year from x.