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
andday
must beINTEGER
. 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 ofyear-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 formatyyyy-MM-dd HH:mm:ss
. Returns null ifstring
does not matchformat
.
- unix_timestamp(string, format) integer
Returns the UNIX timestamp of time specified by
string
using the format described in theformat
string. The format follows Spark’s Datetime patterns for formatting and parsing. Returns null ifstring
does not matchformat
or ifformat
is invalid.
- week_of_year(x) integer ¶
Returns the ISO-Week of the year from x. The value ranges from
1
to53
. 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
.