Date and Time Functions¶
Convenience Extraction Functions¶
These functions support TIMESTAMP and DATE input types.
- add_months(startDate, numMonths) date ¶
Returns the date that is
numMonths
afterstartDate
. Adjusts result to a valid one, considering months have different total days, and especially February has 28 days in common year but 29 days in leap year. For example, add_months(‘2015-01-30’, 1) returns ‘2015-02-28’, because 28th is the last day in February of 2015.numMonths
can be zero or negative. Throws an error when inputs lead to int overflow, e.g., add_months(‘2023-07-10’, -2147483648).SELECT add_months('2015-01-01', 10); -- '2015-11-01' SELECT add_months('2015-01-30', 1); -- '2015-02-28' SELECT add_months('2015-01-30', 0); -- '2015-01-30' SELECT add_months('2015-01-30', -2); -- '2014-11-30' SELECT add_months('2015-03-31', -1); -- '2015-02-28'
- date_add(start_date, num_days) date ¶
Returns the date that is
num_days
afterstart_date
. According to the inputs, the returned date will wrap around between the minimum negative date and maximum positive date. date_add(‘1969-12-31’, 2147483647) get 5881580-07-10, and date_add(‘2024-01-22’, 2147483647) get -5877587-07-12.If
num_days
is a negative value then these amount of days will be deducted fromstart_date
. Supported types fornum_days
are: TINYINT, SMALLINT, INTEGER.
- date_from_unix_date(integer) date ¶
Creates date from the number of days since 1970-01-01 in either direction. Returns null when input is null.
SELECT date_from_unix_date(1); – ‘1970-01-02’ SELECT date_from_unix_date(-1); – ‘1969-12-31’
- date_sub(start_date, num_days) date ¶
Returns the date that is
num_days
beforestart_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. Supported types fornum_days
are: TINYINT, SMALLINT, INTEGER.
- datediff(endDate, startDate) integer ¶
Returns the number of days from startDate to endDate. Only DATE type is allowed for input.
SELECT datediff('2009-07-31', '2009-07-30'); -- 1 SELECT datediff('2009-07-30', '2009-07-31'); -- -1
- dayofmonth(date) integer ¶
Returns the day of month of the date.
SELECT dayofmonth('2009-07-30'); -- 30
- dayofyear(date) integer ¶
Returns the day of year of the date.
SELECT dayofyear('2016-04-09'); -- 100
- dayofweek(date) integer ¶
Returns the day of the week for date (1 = Sunday, 2 = Monday, …, 7 = Saturday).
SELECT dayofweek(‘2009-07-30’); – 5 SELECT dayofweek(‘2023-08-22’); – 3
- from_unixtime(unixTime, format) string ¶
Adjusts
unixTime
(elapsed seconds since UNIX epoch) to configured session timezone, then converts it to a formatted time string according toformat
. Only supports BIGINT type forunixTime
. Using Simple <https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html> date formatter in lenient mode that is align with Spark legacy date parser behavior or Joda <https://www.joda.org/joda-time/> date formatter depends onspark.legacy_date_formatter
configuration. Valid patterns for date format. Throws exception for invalidformat
. This function will convert input to milliseconds, and integer overflow is allowed in the conversion, which aligns with Spark. See the below third example where INT64_MAX is used, -1000 milliseconds are produced by INT64_MAX * 1000 due to integer overflow.SELECT from_unixtime(100, 'yyyy-MM-dd HH:mm:ss'); -- '1970-01-01 00:01:40' SELECT from_unixtime(3600, 'yyyy'); -- '1970' SELECT from_unixtime(9223372036854775807, "yyyy-MM-dd HH:mm:ss"); -- '1969-12-31 23:59:59'
If we run the following query in the Asia/Shanghai time zone:
SELECT from_unixtime(100, 'yyyy-MM-dd HH:mm:ss'); -- '1970-01-01 08:01:40' SELECT from_unixtime(3600, 'yyyy'); -- '1970' SELECT from_unixtime(9223372036854775807, "yyyy-MM-dd HH:mm:ss"); -- '1970-01-01 07:59:59'
- from_utc_timestamp(timestamp, string) timestamp ¶
Returns the timestamp value from UTC timezone to the given timezone.
SELECT from_utc_timestamp('2015-07-24 07:00:00', 'America/Los_Angeles'); -- '2015-07-24 00:00:00'
- get_timestamp(string, dateFormat) timestamp ¶
Returns timestamp by parsing
string
according to the specifieddateFormat
. The format follows Spark’s Datetime patterns. Returns NULL for parsing error or NULL input. Throws exception for invalid format.SELECT get_timestamp('1970-01-01', 'yyyy-MM-dd); -- timestamp `1970-01-01` SELECT get_timestamp('1970-01-01', 'yyyy-MM'); -- NULL (parsing error) SELECT get_timestamp('1970-01-01', null); -- NULL SELECT get_timestamp('2020-06-10', 'A'); -- (throws exception)
- hour(timestamp) integer ¶
Returns the hour of
timestamp
.:SELECT hour('2009-07-30 12:58:59'); -- 12
- 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.
- make_ym_interval([years[, months]]) interval year to month ¶
Make year-month interval from
years
andmonths
fields. Returns the actual year-month with month in the range of [0, 11]. Bothyears
andmonths
can be zero, positive or negative. Throws an error when inputs lead to int overflow, e.g., make_ym_interval(178956970, 8).SELECT make_ym_interval(1, 2); -- 1-2 SELECT make_ym_interval(1, 0); -- 1-0 SELECT make_ym_interval(-1, 1); -- -0-11 SELECT make_ym_interval(1, 100); -- 9-4 SELECT make_ym_interval(1, 12); -- 2-0 SELECT make_ym_interval(1, -12); -- 0-0 SELECT make_ym_interval(2); -- 2-0 SELECT make_ym_interval(); -- 0-0
- minute(timestamp) integer ¶
Returns the minutes of
timestamp
.:SELECT minute('2009-07-30 12:58:59'); -- 58
- quarter(date) integer ¶
Returns the quarter of
date
. The value ranges from1
to4
.SELECT quarter('2009-07-30'); -- 3
- make_timestamp(year, month, day, hour, minute, second[, timezone]) timestamp ¶
Create timestamp from
year
,month
,day
,hour
,minute
andsecond
fields. If thetimezone
parameter is provided, the function interprets the input time components as being in the specifiedtimezone
. Otherwise the function assumes the inputs are in the session’s configured time zone. Requiressession_timezone
to be set, or an exceptions will be thrown.- Arguments:
year - the year to represent, within the Joda datetime
month - the month-of-year to represent, from 1 (January) to 12 (December)
day - the day-of-month to represent, from 1 to 31
hour - the hour-of-day to represent, from 0 to 23
minute - the minute-of-hour to represent, from 0 to 59
second - the second-of-minute and its micro-fraction to represent, from 0 to 60. The value can be either an integer like 13, or a fraction like 13.123. The fractional part can have up to 6 digits to represent microseconds. If the sec argument equals to 60, the seconds field is set to 0 and 1 minute is added to the final timestamp.
timezone - the time zone identifier. For example, CET, UTC and etc.
Returns the timestamp adjusted to the GMT time zone. Returns NULL for invalid or NULL input.
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887); -- 2014-12-28 06:30:45.887 SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET'); -- 2014-12-28 05:30:45.887 SELECT make_timestamp(2019, 6, 30, 23, 59, 60); -- 2019-07-01 00:00:00 SELECT make_timestamp(2019, 6, 30, 23, 59, 1); -- 2019-06-30 23:59:01 SELECT make_timestamp(null, 7, 22, 15, 30, 0); -- NULL SELECT make_timestamp(2014, 12, 28, 6, 30, 60.000001); -- NULL SELECT make_timestamp(2014, 13, 28, 6, 30, 45.887); -- NULL
- month(date) integer ¶
Returns the month of
date
.SELECT month('2009-07-30'); -- 7
- next_day(startDate, dayOfWeek) date ¶
Returns the first date which is later than
startDate
and named asdayOfWeek
. Returns null ifdayOfWeek
is invalid.dayOfWeek
is case insensitive and must be one of the following:SU
,SUN
,SUNDAY
,MO
,MON
,MONDAY
,TU
,TUE
,TUESDAY
,WE
,WED
,WEDNESDAY
,TH
,THU
,THURSDAY
,FR
,FRI
,FRIDAY
,SA
,SAT
,SATURDAY
.SELECT next_day('2015-07-23', "Mon"); -- '2015-07-27' SELECT next_day('2015-07-23', "mo"); -- '2015-07-27' SELECT next_day('2015-07-23', "Tue"); -- '2015-07-28' SELECT next_day('2015-07-23', "tu"); -- '2015-07-28' SELECT next_day('2015-07-23', "we"); -- '2015-07-29'
- second(timestamp) integer ¶
Returns the seconds of
timestamp
.SELECT second('2009-07-30 12:58:59'); -- 59
- timestamp_micros(x) timestamp ¶
Returns timestamp from the number of microseconds since UTC epoch. Supported types are: TINYINT, SMALLINT, INTEGER and BIGINT.:
SELECT timestamp_micros(1230219000123123); -- '2008-12-25 15:30:00.123123'
- timestamp_millis(x) timestamp ¶
Returns timestamp from the number of milliseconds since UTC epoch. Supported types are: TINYINT, SMALLINT, INTEGER and BIGINT.:
SELECT timestamp_millis(1230219000123); -- '2008-12-25 15:30:00.123'
- to_unix_timestamp(string) integer ¶
Alias for
unix_timestamp(string) -> integer
.
- to_unix_timestamp(string, format) integer
Alias for
unix_timestamp(string, format) -> integer
.
- to_utc_timestamp(timestamp, string) timestamp ¶
Returns the timestamp value from the given timezone to UTC timezone.
SELECT to_utc_timestamp('2015-07-24 00:00:00', 'America/Los_Angeles'); -- '2015-07-24 07:00:00'
- unix_date(date) integer ¶
Returns the number of days since 1970-01-01.
SELECT unix_date('1970-01-01'); -- '0' SELECT unix_date('1970-01-02'); -- '1' SELECT unix_date('1969-12-31'); -- '-1'
- unix_micros(timestamp) bigint ¶
Returns the number of microseconds since 1970-01-01 00:00:00 UTC.:
SELECT unix_micros('1970-01-01 00:00:01'); -- 1000000
- unix_millis(timestamp) bigint ¶
Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.:
SELECT unix_millis('1970-01-01 00:00:01'); -- 1000
- unix_seconds(timestamp) bigint ¶
Returns the number of seconds since 1970-01-01 00:00:00 UTC.
SELECT unix_seconds('1970-01-01 00:00:01'); -- 1
- unix_timestamp() integer ¶
Returns the current UNIX timestamp in seconds. Using Simple <https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html> date formatter in lenient mode that is align with Spark legacy date parser behavior or Joda <https://www.joda.org/joda-time/> date formatter depends on the
spark.legacy_date_formatter
configuration.
- 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.
- weekday(date) integer ¶
Returns the day of the week for date (0 = Monday, 1 = Tuesday, …, 6 = Sunday).
SELECT weekday('2015-04-08'); -- 2 SELECT weekday('2024-02-10'); -- 5
- year(x) integer ¶
Returns the year from
x
.
- year_of_week(x) integer ¶
Returns the ISO week-numbering year that
x
falls in. For example, 2005-01-02 is part of the 53rd week of year 2004, so the result is 2004. Only supports DATE type.SELECT year_of_week(‘2005-01-02’); – 2004