Datetime Functions ¶

This page lists all datetime functions available in Spark SQL.

current_date ¶

current_date() - Returns the current date at the start of query evaluation. All calls of current_date within the same query return the same value.

current_date - Returns the current date at the start of query evaluation.

Examples:

> SELECT current_date();
 2020-04-25
> SELECT current_date;
 2020-04-25

Note:

The syntax without braces has been supported since 2.0.1.

Since: 1.5.0

current_timestamp ¶

current_timestamp() - Returns the current timestamp at the start of query evaluation. All calls of current_timestamp within the same query return the same value.

current_timestamp - Returns the current timestamp at the start of query evaluation.

Examples:

> SELECT current_timestamp();
 2020-04-25 15:49:11.914
> SELECT current_timestamp;
 2020-04-25 15:49:11.914

Note:

The syntax without braces has been supported since 2.0.1.

Since: 1.5.0

date_part ¶

date_part(field, source) - Extracts a part of the date/timestamp or interval source.

Arguments:

field - selects which part of the source should be extracted, and supported string values are as same as the fields of the equivalent function EXTRACT.
source - a date/timestamp or interval column from where field should be extracted

Examples:

> SELECT date_part('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
 2019
> SELECT date_part('week', timestamp'2019-08-12 01:00:00.123456');
 33
> SELECT date_part('doy', DATE'2019-08-12');
 224
> SELECT date_part('SECONDS', timestamp'2019-10-01 00:00:01.000001');
 1.000001
> SELECT date_part('days', interval 5 days 3 hours 7 minutes);
 5
> SELECT date_part('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
 30.001001
> SELECT date_part('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
 11
> SELECT date_part('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
 55

Note:

The date_part function is equivalent to the SQL-standard function EXTRACT(field FROM source)

Since: 3.0.0

datepart ¶

datepart(field, source) - Extracts a part of the date/timestamp or interval source.

Arguments:

field - selects which part of the source should be extracted, and supported string values are as same as the fields of the equivalent function EXTRACT.
source - a date/timestamp or interval column from where field should be extracted

Examples:

> SELECT datepart('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
 2019
> SELECT datepart('week', timestamp'2019-08-12 01:00:00.123456');
 33
> SELECT datepart('doy', DATE'2019-08-12');
 224
> SELECT datepart('SECONDS', timestamp'2019-10-01 00:00:01.000001');
 1.000001
> SELECT datepart('days', interval 5 days 3 hours 7 minutes);
 5
> SELECT datepart('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
 30.001001
> SELECT datepart('MONTH', INTERVAL '2021-11' YEAR TO MONTH);
 11
> SELECT datepart('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND);
 55

Note:

The datepart function is equivalent to the SQL-standard function EXTRACT(field FROM source)

Since: 3.4.0

extract ¶

extract(field FROM source) - Extracts a part of the date or timestamp or time or interval source.

Arguments:

field - selects which part of the source should be extracted
- Supported string values of field for dates and timestamps are(case insensitive):
  - "YEAR", ("Y", "YEARS", "YR", "YRS") - the year field
  - "YEAROFWEEK" - the ISO 8601 week-numbering year that the datetime falls in. For example, 2005-01-02 is part of the 53rd week of year 2004, so the result is 2004
  - "QUARTER", ("QTR") - the quarter (1 - 4) of the year that the datetime falls in
  - "MONTH", ("MON", "MONS", "MONTHS") - the month field (1 - 12)
  - "WEEK", ("W", "WEEKS") - the number of the ISO 8601 week-of-week-based-year. A week is considered to start on a Monday and week 1 is the first week with >3 days. In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-02 is part of the 53rd week of year 2004, while 2012-12-31 is part of the first week of 2013
  - "DAY", ("D", "DAYS") - the day of the month field (1 - 31)
  - "DAYOFWEEK",("DOW") - the day of the week for datetime as Sunday(1) to Saturday(7)
  - "DAYOFWEEK_ISO",("DOW_ISO") - ISO 8601 based day of the week for datetime as Monday(1) to Sunday(7)
  - "DOY" - the day of the year (1 - 365/366)
  - "HOUR", ("H", "HOURS", "HR", "HRS") - The hour field (0 - 23)
  - "MINUTE", ("M", "MIN", "MINS", "MINUTES") - the minutes field (0 - 59)
  - "SECOND", ("S", "SEC", "SECONDS", "SECS") - the seconds field, including fractional parts
- Supported string values of field for interval(which consists of months, days, microseconds) are(case insensitive):
  - "YEAR", ("Y", "YEARS", "YR", "YRS") - the total months / 12
  - "MONTH", ("MON", "MONS", "MONTHS") - the total months % 12
  - "DAY", ("D", "DAYS") - the days part of interval
  - "HOUR", ("H", "HOURS", "HR", "HRS") - how many hours the microseconds contains
  - "MINUTE", ("M", "MIN", "MINS", "MINUTES") - how many minutes left after taking hours from microseconds
  - "SECOND", ("S", "SEC", "SECONDS", "SECS") - how many second with fractions left after taking hours and minutes from microseconds
- Supported string values of field for time (which consists of hour, minute, second) are(case insensitive):
  - "HOUR", ("H", "HOURS", "HR", "HRS") - The hour field (0 - 23)
  - "MINUTE", ("M", "MIN", "MINS", "MINUTES") - the minutes field (0 - 59)
  - "SECOND", ("S", "SEC", "SECONDS", "SECS") - the seconds field, including fractional parts up to micro second precision. Returns a DECIMAL(8, 6) precision value.
source - a date or timestamp or time or interval column from where field should be extracted

Examples:

> SELECT extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456');
 2019
> SELECT extract(week FROM timestamp'2019-08-12 01:00:00.123456');
 33
> SELECT extract(doy FROM DATE'2019-08-12');
 224
> SELECT extract(SECONDS FROM timestamp'2019-10-01 00:00:01.000001');
 1.000001
> SELECT extract(days FROM interval 5 days 3 hours 7 minutes);
 5
> SELECT extract(seconds FROM interval 5 hours 30 seconds 1 milliseconds 1 microseconds);
 30.001001
> SELECT extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH);
 11
> SELECT extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND);
 55
> SELECT extract(HOUR FROM time '09:08:01.000001');
 9
> SELECT extract(MINUTE FROM time '09:08:01.000001');
 8
> SELECT extract(SECOND FROM time '09:08:01.000001');
 1.000001

Note:

The extract function is equivalent to date_part(field, source).

Since: 3.0.0

from_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14 03:40:00.0'.

Examples:

> SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul');
 2016-08-31 09:00:00

Since: 1.5.0

hour ¶

hour(expr) - Returns the hour component of the given expression.

If expr is a TIMESTAMP or a string that can be cast to timestamp, it returns the hour of that timestamp. If expr is a TIME type (since 4.1.0), it returns the hour of the time-of-day.

Examples:

> SELECT hour('2018-02-14 12:58:59');
 12
> SELECT hour(TIME'13:59:59.999999');
 13

Since: 1.5.0

last_day ¶

last_day(date) - Returns the last day of the month which the date belongs to.

Examples:

> SELECT last_day('2009-01-12');
 2009-01-31

Since: 1.5.0

localtimestamp ¶

localtimestamp() - Returns the current timestamp without time zone at the start of query evaluation. All calls of localtimestamp within the same query return the same value.

localtimestamp - Returns the current local date-time at the session time zone at the start of query evaluation.

Examples:

> SELECT localtimestamp();
 2020-04-25 15:49:11.914

Since: 3.4.0

make_date ¶

make_date(year, month, day) - Create date from year, month and day fields. If the configuration spark.sql.ansi.enabled is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.

Arguments:

year - the year to represent, from 1 to 9999
month - the month-of-year to represent, from 1 (January) to 12 (December)
day - the day-of-month to represent, from 1 to 31

Examples:

> SELECT make_date(2013, 7, 15);
 2013-07-15
> SELECT make_date(2019, 7, NULL);
 NULL

Since: 3.0.0

make_dt_interval ¶

make_dt_interval([days[, hours[, mins[, secs]]]]) - Make DayTimeIntervalType duration from days, hours, mins and secs.

Arguments:

days - the number of days, positive or negative
hours - the number of hours, positive or negative
mins - the number of minutes, positive or negative
secs - the number of seconds with the fractional part in microsecond precision.

Examples:

> SELECT make_dt_interval(1, 12, 30, 01.001001);
 1 12:30:01.001001000
> SELECT make_dt_interval(2);
 2 00:00:00.000000000
> SELECT make_dt_interval(100, null, 3);
 NULL

Since: 3.2.0

make_interval ¶

make_interval([years[, months[, weeks[, days[, hours[, mins[, secs]]]]]]]) - Make interval from years, months, weeks, days, hours, mins and secs.

Arguments:

years - the number of years, positive or negative
months - the number of months, positive or negative
weeks - the number of weeks, positive or negative
days - the number of days, positive or negative
hours - the number of hours, positive or negative
mins - the number of minutes, positive or negative
secs - the number of seconds with the fractional part in microsecond precision.

Examples:

> SELECT make_interval(100, 11, 1, 1, 12, 30, 01.001001);
 100 years 11 months 8 days 12 hours 30 minutes 1.001001 seconds
> SELECT make_interval(100, null, 3);
 NULL
> SELECT make_interval(0, 1, 0, 1, 0, 0, 100.000001);
 1 months 1 days 1 minutes 40.000001 seconds

Since: 3.0.0

make_time ¶

make_time(hour, minute, second) - Create time from hour, minute and second fields. For invalid inputs it will throw an error.

Arguments:

hour - the hour to represent, from 0 to 23
minute - the minute to represent, from 0 to 59
second - the second to represent, from 0 to 59.999999

Examples:

> SELECT make_time(6, 30, 45.887);
 06:30:45.887
> SELECT make_time(NULL, 30, 0);
 NULL

Since: 4.1.0

make_timestamp ¶

make_timestamp(year, month, day, hour, min, sec[, timezone]) - Create the current timestamp with local time zone from year, month, day, hour, min, sec and timezone fields. If the configuration spark.sql.ansi.enabled is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.

make_timestamp(date[, time[, timezone]]) - Create timestamp from date and time fields.

Arguments:

year - the year to represent, from 1 to 9999
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
min - the minute-of-hour to represent, from 0 to 59
sec - the second-of-minute and its micro-fraction to represent, from 0 to 60. If the sec argument equals to 60, the seconds field is set to 0 and 1 minute is added to the final timestamp.
date - a date expression
time - a time expression (optional). Default is 00:00:00.
timezone - the time zone identifier (optional). For example, CET, UTC and etc.

Examples:

> 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-27 21:30:45.887
> SELECT make_timestamp(DATE'2014-12-28');
 2014-12-28 00:00:00
> SELECT make_timestamp(DATE'2014-12-28', TIME'6:30:45.887');
 2014-12-28 06:30:45.887
> SELECT make_timestamp(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
 2014-12-27 21:30:45.887

Since: 3.0.0

make_timestamp_ltz ¶

make_timestamp_ltz(year, month, day, hour, min, sec[, timezone]) - Create the current timestamp with local time zone from year, month, day, hour, min, sec and (optional) timezone fields. If the configuration spark.sql.ansi.enabled is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.

make_timestamp_ltz(date, time[, timezone]) - Create a local date-time from date, time and (optional) timezone fields.

Arguments:

year - the year to represent, from 1 to 9999
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
min - the minute-of-hour to represent, from 0 to 59
sec - the second-of-minute and its micro-fraction to represent, from 0 to 60. 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.
date - a date to represent, from 0001-01-01 to 9999-12-31
time - a local time to represent, from 00:00:00 to 23:59:59.999999

Examples:

> SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887);
 2014-12-28 06:30:45.887
> SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, 'CET');
 2014-12-27 21:30:45.887
> SELECT make_timestamp_ltz(2019, 6, 30, 23, 59, 60);
 2019-07-01 00:00:00
> SELECT make_timestamp_ltz(null, 7, 22, 15, 30, 0);
 NULL
> SELECT make_timestamp_ltz(DATE'2014-12-28', TIME'6:30:45.887');
 2014-12-28 06:30:45.887
> SELECT make_timestamp_ltz(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
 2014-12-27 21:30:45.887

Since: 3.4.0

make_timestamp_ntz ¶

make_timestamp_ntz(year, month, day, hour, min, sec) - Create local date-time from year, month, day, hour, min, sec fields. If the configuration spark.sql.ansi.enabled is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.

make_timestamp_ntz(date, time) - Create a local date-time from date and time fields.

Arguments:

year - the year to represent, from 1 to 9999
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
min - the minute-of-hour to represent, from 0 to 59
sec - the second-of-minute and its micro-fraction to represent, from 0 to 60. If the sec argument equals to 60, the seconds field is set to 0 and 1 minute is added to the final timestamp.
date - a date to represent, from 0001-01-01 to 9999-12-31
time - a local time to represent, from 00:00:00 to 23:59:59.999999

Examples:

> SELECT make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887);
 2014-12-28 06:30:45.887
> SELECT make_timestamp_ntz(2019, 6, 30, 23, 59, 60);
 2019-07-01 00:00:00
> SELECT make_timestamp_ntz(null, 7, 22, 15, 30, 0);
 NULL
> SELECT make_timestamp_ntz(DATE'2014-12-28', TIME'6:30:45.887');
 2014-12-28 06:30:45.887

Since: 3.4.0

make_ym_interval ¶

make_ym_interval([years[, months]]) - Make year-month interval from years, months.

Arguments:

years - the number of years, positive or negative
months - the number of months, positive or negative

Examples:

> 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(2);
 2-0

Since: 3.2.0

minute ¶

minute(expr) - Returns the minute component of the given expression.

If expr is a TIMESTAMP or a string that can be cast to timestamp, it returns the minute of that timestamp. If expr is a TIME type (since 4.1.0), it returns the minute of the time-of-day.

Examples:

> SELECT minute('2009-07-30 12:58:59');
 58
> SELECT minute(TIME'23:59:59.999999');
 59

Since: 1.5.0

month ¶

month(date) - Returns the month component of the date/timestamp.

Examples:

> SELECT month('2016-07-30');
 7

Since: 1.5.0

monthname ¶

monthname(date) - Returns the three-letter abbreviated month name from the given date.

Examples:

> SELECT monthname('2008-02-20');
 Feb

Since: 4.0.0

months_between ¶

months_between(timestamp1, timestamp2[, roundOff]) - If timestamp1 is later than timestamp2, then the result is positive. If timestamp1 and timestamp2 are on the same day of month, or both are the last day of month, time of day will be ignored. Otherwise, the difference is calculated based on 31 days per month, and rounded to 8 digits unless roundOff=false.

Examples:

> SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
 3.94959677
> SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false);
 3.9495967741935485

Since: 1.5.0

next_day ¶

next_day(start_date, day_of_week) - Returns the first date which is later than start_date and named as indicated. The function returns NULL if at least one of the input parameters is NULL. When both of the input parameters are not NULL and day_of_week is an invalid input, the function throws SparkIllegalArgumentException if spark.sql.ansi.enabled is set to true, otherwise NULL.

Examples:

> SELECT next_day('2015-01-14', 'TU');
 2015-01-20

Since: 1.5.0

now ¶

now() - Returns the current timestamp at the start of query evaluation.

Examples:

> SELECT now();
 2020-04-25 15:49:11.914

Since: 1.6.0

quarter ¶

quarter(date) - Returns the quarter of the year for date, in the range 1 to 4.

Examples:

> SELECT quarter('2016-08-31');
 3

Since: 1.5.0

second ¶

second(expr) - Returns the second component of the given expression.

If expr is a TIMESTAMP or a string that can be cast to timestamp, it returns the second of that timestamp. If expr is a TIME type (since 4.1.0), it returns the second of the time-of-day.

Examples:

> SELECT second('2018-02-14 12:58:59');
 59
> SELECT second(TIME'13:25:59.999999');
 59

Since: 1.5.0

session_window ¶

session_window(time_column, gap_duration) - Generates session window given a timestamp specifying column and gap duration. See 'Types of time windows' in Structured Streaming guide doc for detailed explanation and examples.

Arguments:

time_column - The column or the expression to use as the timestamp for windowing by time. The time column must be of TimestampType.
gap_duration - A string specifying the timeout of the session represented as "interval value" (See Interval Literal for more details.) for the fixed gap duration, or an expression which is applied for each input and evaluated to the "interval value" for the dynamic gap duration.

Examples:

> SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, session_window(b, '5 minutes') ORDER BY a, start;
  A1    2021-01-01 00:00:00 2021-01-01 00:09:30 2
  A1    2021-01-01 00:10:00 2021-01-01 00:15:00 1
  A2    2021-01-01 00:01:00 2021-01-01 00:06:00 1
> SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00'), ('A2', '2021-01-01 00:04:30') AS tab(a, b) GROUP by a, session_window(b, CASE WHEN a = 'A1' THEN '5 minutes' WHEN a = 'A2' THEN '1 minute' ELSE '10 minutes' END) ORDER BY a, start;
  A1    2021-01-01 00:00:00 2021-01-01 00:09:30 2
  A1    2021-01-01 00:10:00 2021-01-01 00:15:00 1
  A2    2021-01-01 00:01:00 2021-01-01 00:02:00 1
  A2    2021-01-01 00:04:30 2021-01-01 00:05:30 1

Since: 3.2.0

time_diff ¶

time_diff(unit, start, end) - Gets the difference between the times in the specified units.

Arguments:

unit - the unit of the difference between the given times
- "HOUR"
- "MINUTE"
- "SECOND"
- "MILLISECOND"
- "MICROSECOND"
start - a starting TIME expression
end - an ending TIME expression

Examples:

> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:28');
 0
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:29');
 1
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'12:00:00');
 -8

Since: 4.1.0

time_from_micros ¶

time_from_micros(micros) - Creates a TIME value from microseconds since midnight.

Arguments:

micros - microseconds since midnight (0 to 86399999999)

Examples:

> SELECT time_from_micros(0);
 00:00:00
> SELECT time_from_micros(52200000000);
 14:30:00
> SELECT time_from_micros(52200500000);
 14:30:00.5
> SELECT time_from_micros(86399999999);
 23:59:59.999999

Since: 4.2.0

time_from_millis ¶

time_from_millis(millis) - Creates a TIME value from milliseconds since midnight.

Arguments:

millis - milliseconds since midnight (0 to 86399999)

Examples:

> SELECT time_from_millis(0);
 00:00:00
> SELECT time_from_millis(52200000);
 14:30:00
> SELECT time_from_millis(52200500);
 14:30:00.5
> SELECT time_from_millis(86399999);
 23:59:59.999

Since: 4.2.0

time_from_seconds ¶

time_from_seconds(seconds) - Creates a TIME value from seconds since midnight.

Arguments:

seconds - seconds since midnight (0 to 86399.999999). Supports decimals for fractional seconds.

Examples:

> SELECT time_from_seconds(0);
 00:00:00
> SELECT time_from_seconds(52200);
 14:30:00
> SELECT time_from_seconds(52200.5);
 14:30:00.5
> SELECT time_from_seconds(86399.999999);
 23:59:59.999999

Since: 4.2.0

time_to_micros ¶

time_to_micros(time) - Returns the number of microseconds since midnight for the given TIME value.

Arguments:

time - TIME value to convert

Examples:

> SELECT time_to_micros(TIME'00:00:00');
 0
> SELECT time_to_micros(TIME'14:30:00');
 52200000000
> SELECT time_to_micros(TIME'14:30:00.5');
 52200500000
> SELECT time_to_micros(TIME'23:59:59.999999');
 86399999999

Since: 4.2.0

time_to_millis ¶

time_to_millis(time) - Returns the number of milliseconds since midnight for the given TIME value.

Arguments:

time - TIME value to convert

Examples:

> SELECT time_to_millis(TIME'00:00:00');
 0
> SELECT time_to_millis(TIME'14:30:00');
 52200000
> SELECT time_to_millis(TIME'14:30:00.5');
 52200500
> SELECT time_to_millis(TIME'23:59:59.999');
 86399999

Since: 4.2.0

time_to_seconds ¶

time_to_seconds(time) - Returns the number of seconds since midnight for the given TIME value.

Arguments:

time - TIME value to convert

Examples:

> SELECT time_to_seconds(TIME'00:00:00');
 0.000000
> SELECT time_to_seconds(TIME'14:30:00');
 52200.000000
> SELECT time_to_seconds(TIME'14:30:00.5');
 52200.500000
> SELECT time_to_seconds(TIME'23:59:59.999999');
 86399.999999

Since: 4.2.0

time_trunc ¶

time_trunc(unit, time) - Returns time truncated to the unit.

Arguments:

unit - the unit to truncate to
- "HOUR" - zero out the minutes and seconds with fraction part
- "MINUTE" - zero out the seconds with fraction part
- "SECOND" - zero out the fraction part of seconds
- "MILLISECOND" - zero out the microseconds
- "MICROSECOND" - zero out the nanoseconds
time - a TIME expression

Examples:

> SELECT time_trunc('HOUR', TIME'09:32:05.359');
 09:00:00
> SELECT time_trunc('MILLISECOND', TIME'09:32:05.123456');
 09:32:05.123

Since: 4.1.0

timestamp_micros ¶

timestamp_micros(microseconds) - Creates timestamp from the number of microseconds since UTC epoch.

Examples:

> SELECT timestamp_micros(1230219000123123);
 2008-12-25 07:30:00.123123

Since: 3.1.0

timestamp_millis ¶

timestamp_millis(milliseconds) - Creates timestamp from the number of milliseconds since UTC epoch.

Examples:

> SELECT timestamp_millis(1230219000123);
 2008-12-25 07:30:00.123

Since: 3.1.0

timestamp_seconds ¶

timestamp_seconds(seconds) - Creates timestamp from the number of seconds (can be fractional) since UTC epoch.

Examples:

> SELECT timestamp_seconds(1230219000);
 2008-12-25 07:30:00
> SELECT timestamp_seconds(1230219000.123);
 2008-12-25 07:30:00.123

Since: 3.1.0

to_date ¶

to_date(date_str[, fmt]) - Parses the date_str expression with the fmt expression to a date. Returns null with invalid input. By default, it follows casting rules to a date if the fmt is omitted.

Arguments:

date_str - A string to be parsed to date.
fmt - Date format pattern to follow. See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT to_date('2009-07-30 04:17:52');
 2009-07-30
> SELECT to_date('2016-12-31', 'yyyy-MM-dd');
 2016-12-31

Since: 1.5.0

to_time ¶

to_time(str[, format]) - Parses the str expression with the format expression to a time. If format is malformed or its application does not result in a well formed time, the function raises an error. By default, it follows casting rules to a time if the format is omitted.

Arguments:

str - A string to be parsed to time.
format - Time format pattern to follow. See Datetime Patterns for valid time format patterns.

Examples:

> SELECT to_time('00:12:00');
 00:12:00
> SELECT to_time('12.10.05', 'HH.mm.ss');
 12:10:05

Since: 4.1.0

to_timestamp ¶

to_timestamp(timestamp_str[, fmt]) - Parses the timestamp_str expression with the fmt expression to a timestamp. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted. The result data type is consistent with the value of configuration spark.sql.timestampType.

Arguments:

timestamp_str - A string to be parsed to timestamp.
fmt - Timestamp format pattern to follow. See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT to_timestamp('2016-12-31 00:12:00');
 2016-12-31 00:12:00
> SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd');
 2016-12-31 00:00:00

Since: 2.2.0

to_timestamp_ltz ¶

to_timestamp_ltz(timestamp_str[, fmt]) - Parses the timestamp_str expression with the fmt expression to a timestamp with local time zone. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted.

Arguments:

timestamp_str - A string to be parsed to timestamp with local time zone.
fmt - Timestamp format pattern to follow. See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT to_timestamp_ltz('2016-12-31 00:12:00');
 2016-12-31 00:12:00
> SELECT to_timestamp_ltz('2016-12-31', 'yyyy-MM-dd');
 2016-12-31 00:00:00

Since: 3.4.0

to_timestamp_ntz ¶

to_timestamp_ntz(timestamp_str[, fmt]) - Parses the timestamp_str expression with the fmt expression to a timestamp without time zone. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted.

Arguments:

timestamp_str - A string to be parsed to timestamp without time zone.
fmt - Timestamp format pattern to follow. See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT to_timestamp_ntz('2016-12-31 00:12:00');
 2016-12-31 00:12:00
> SELECT to_timestamp_ntz('2016-12-31', 'yyyy-MM-dd');
 2016-12-31 00:00:00

Since: 3.4.0

to_unix_timestamp ¶

to_unix_timestamp(timeExp[, fmt]) - Returns the UNIX timestamp of the given time.

Arguments:

timeExp - A date/timestamp or string which is returned as a UNIX timestamp.
fmt - Date/time format pattern to follow. Ignored if timeExp is not a string. Default value is "yyyy-MM-dd HH:mm:ss". See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd');
 1460098800

Since: 1.6.0

to_utc_timestamp ¶

to_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, 'GMT+1' would yield '2017-07-14 01:40:00.0'.

Examples:

> SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul');
 2016-08-30 15:00:00

Since: 1.5.0

trunc ¶

trunc(date, fmt) - Returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Arguments:

date - date value or valid date string
fmt - the format representing the unit to be truncated to
- "YEAR", "YYYY", "YY" - truncate to the first date of the year that the date falls in
- "QUARTER" - truncate to the first date of the quarter that the date falls in
- "MONTH", "MM", "MON" - truncate to the first date of the month that the date falls in
- "WEEK" - truncate to the Monday of the week that the date falls in

Examples:

> SELECT trunc('2019-08-04', 'week');
 2019-07-29
> SELECT trunc('2019-08-04', 'quarter');
 2019-07-01
> SELECT trunc('2009-02-12', 'MM');
 2009-02-01
> SELECT trunc('2015-10-27', 'YEAR');
 2015-01-01

Since: 1.5.0

try_make_interval ¶

try_make_interval([years[, months[, weeks[, days[, hours[, mins[, secs]]]]]]]) - This is a special version of make_interval that performs the same operation, but returns NULL when an overflow occurs.

Arguments:

years - the number of years, positive or negative
months - the number of months, positive or negative
weeks - the number of weeks, positive or negative
days - the number of days, positive or negative
hours - the number of hours, positive or negative
mins - the number of minutes, positive or negative
secs - the number of seconds with the fractional part in microsecond precision.

Examples:

> SELECT try_make_interval(100, 11, 1, 1, 12, 30, 01.001001);
 100 years 11 months 8 days 12 hours 30 minutes 1.001001 seconds
> SELECT try_make_interval(100, null, 3);
 NULL
> SELECT try_make_interval(0, 1, 0, 1, 0, 0, 100.000001);
 1 months 1 days 1 minutes 40.000001 seconds
> SELECT try_make_interval(2147483647);
 NULL

Since: 4.0.0

try_make_timestamp ¶

try_make_timestamp(year, month, day, hour, min, sec[, timezone]) - Try to create a timestamp from year, month, day, hour, min, sec and timezone fields. The result data type is consistent with the value of configuration spark.sql.timestampType. The function returns NULL on invalid inputs.

try_make_timestamp(date[, time[, timezone]]) - Try to create a timestamp from date, time, and timezone fields.

Arguments:

year - the year to represent, from 1 to 9999
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
min - the minute-of-hour to represent, from 0 to 59
sec - 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. If the sec argument equals to 60, the seconds field is set to 0 and 1 minute is added to the final timestamp.
date - a date expression
time - a time expression (optional). Default is 00:00:00.
timezone - the time zone identifier (optional). For example, CET, UTC and etc.

Examples:

> SELECT try_make_timestamp(2014, 12, 28, 6, 30, 45.887);
 2014-12-28 06:30:45.887
> SELECT try_make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET');
 2014-12-27 21:30:45.887
> SELECT try_make_timestamp(DATE'2014-12-28');
 2014-12-28 00:00:00
> SELECT try_make_timestamp(DATE'2014-12-28', TIME'6:30:45.887');
 2014-12-28 06:30:45.887
> SELECT try_make_timestamp(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
 2014-12-27 21:30:45.887
> SELECT try_make_timestamp(2019, 6, 30, 23, 59, 60);
 2019-07-01 00:00:00
> SELECT try_make_timestamp(2019, 6, 30, 23, 59, 1);
 2019-06-30 23:59:01
> SELECT try_make_timestamp(null, 7, 22, 15, 30, 0);
 NULL
> SELECT try_make_timestamp(2024, 13, 22, 15, 30, 0);
 NULL

Since: 4.0.0

try_make_timestamp_ltz ¶

try_make_timestamp_ltz(year, month, day, hour, min, sec[, timezone]) - Try to create the current timestamp with local time zone from year, month, day, hour, min, sec and (optional) timezone fields. The function returns NULL on invalid inputs.

try_make_timestamp_ltz(date, time[, timezone]) - Try to create the current timestamp with local time zone from date, time and (optional) timezone fields.

Arguments:

year - the year to represent, from 1 to 9999
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
min - the minute-of-hour to represent, from 0 to 59
sec - the second-of-minute and its micro-fraction to represent, from 0 to 60. 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.
date - a date to represent, from 0001-01-01 to 9999-12-31
time - a local time to represent, from 00:00:00 to 23:59:59.999999

Examples:

> SELECT try_make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887);
 2014-12-28 06:30:45.887
> SELECT try_make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, 'CET');
 2014-12-27 21:30:45.887
> SELECT try_make_timestamp_ltz(2019, 6, 30, 23, 59, 60);
 2019-07-01 00:00:00
> SELECT try_make_timestamp_ltz(null, 7, 22, 15, 30, 0);
 NULL
> SELECT try_make_timestamp_ltz(2024, 13, 22, 15, 30, 0);
 NULL
> SELECT try_make_timestamp_ltz(DATE'2014-12-28', TIME'6:30:45.887');
 2014-12-28 06:30:45.887
> SELECT try_make_timestamp_ltz(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
 2014-12-27 21:30:45.887

Since: 4.0.0

try_make_timestamp_ntz ¶

try_make_timestamp_ntz(year, month, day, hour, min, sec) - Try to create local date-time from year, month, day, hour, min, sec fields. The function returns NULL on invalid inputs.

try_make_timestamp_ntz(date, time) - Create a local date-time from date and time fields.

Arguments:

year - the year to represent, from 1 to 9999
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
min - the minute-of-hour to represent, from 0 to 59
sec - the second-of-minute and its micro-fraction to represent, from 0 to 60. If the sec argument equals to 60, the seconds field is set to 0 and 1 minute is added to the final timestamp.
date - a date to represent, from 0001-01-01 to 9999-12-31
time - a local time to represent, from 00:00:00 to 23:59:59.999999

Examples:

> SELECT try_make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887);
 2014-12-28 06:30:45.887
> SELECT try_make_timestamp_ntz(2019, 6, 30, 23, 59, 60);
 2019-07-01 00:00:00
> SELECT try_make_timestamp_ntz(null, 7, 22, 15, 30, 0);
 NULL
> SELECT try_make_timestamp_ntz(2024, 13, 22, 15, 30, 0);
 NULL
> SELECT try_make_timestamp_ntz(DATE'2014-12-28', TIME'6:30:45.887');
 2014-12-28 06:30:45.887

Since: 4.0.0

try_to_date ¶

try_to_date(date_str[, fmt]) - Parses the date_str expression with the fmt expression to a date. The function always returns null on an invalid input with/without ANSI SQL mode enabled. By default, it follows casting rules to a date if the fmt is omitted.

Arguments:

date_str - A string to be parsed to date.
fmt - Date format pattern to follow. See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT try_to_date('2016-12-31');
 2016-12-31
> SELECT try_to_date('2016-12-31', 'yyyy-MM-dd');
 2016-12-31
> SELECT try_to_date('foo', 'yyyy-MM-dd');
 NULL

Since: 4.0.0

try_to_time ¶

try_to_time(str[, format]) - Parses the str expression with the format expression to a time. If format is malformed or its application does not result in a well formed time, the function returns NULL. By default, it follows casting rules to a time if the format is omitted.

Arguments:

str - A string to be parsed to time.
format - Time format pattern to follow. See Datetime Patterns for valid time format patterns.

Examples:

> SELECT try_to_time('00:12:00.001');
 00:12:00.001
> SELECT try_to_time('12.10.05.999999', 'HH.mm.ss.SSSSSS');
 12:10:05.999999
> SELECT try_to_time('foo', 'HH:mm:ss');
 NULL

Since: 4.1.0

try_to_timestamp ¶

try_to_timestamp(timestamp_str[, fmt]) - Parses the timestamp_str expression with the fmt expression to a timestamp. The function always returns null on an invalid input with/without ANSI SQL mode enabled. By default, it follows casting rules to a timestamp if the fmt is omitted. The result data type is consistent with the value of configuration spark.sql.timestampType.

Arguments:

timestamp_str - A string to be parsed to timestamp.
fmt - Timestamp format pattern to follow. See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT try_to_timestamp('2016-12-31 00:12:00');
 2016-12-31 00:12:00
> SELECT try_to_timestamp('2016-12-31', 'yyyy-MM-dd');
 2016-12-31 00:00:00
> SELECT try_to_timestamp('foo', 'yyyy-MM-dd');
 NULL

Since: 3.4.0

unix_date ¶

unix_date(date) - Returns the number of days since 1970-01-01.

Examples:

> SELECT unix_date(DATE("1970-01-02"));
 1

Since: 3.1.0

unix_micros ¶

unix_micros(timestamp) - Returns the number of microseconds since 1970-01-01 00:00:00 UTC.

Examples:

> SELECT unix_micros(TIMESTAMP('1970-01-01 00:00:01Z'));
 1000000

Since: 3.1.0

unix_millis ¶

unix_millis(timestamp) - Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.

Examples:

> SELECT unix_millis(TIMESTAMP('1970-01-01 00:00:01Z'));
 1000

Since: 3.1.0

unix_seconds ¶

unix_seconds(timestamp) - Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.

Examples:

> SELECT unix_seconds(TIMESTAMP('1970-01-01 00:00:01Z'));
 1

Since: 3.1.0

unix_timestamp ¶

unix_timestamp([timeExp[, fmt]]) - Returns the UNIX timestamp of current or specified time.

Arguments:

timeExp - A date/timestamp or string. If not provided, this defaults to current time.
fmt - Date/time format pattern to follow. Ignored if timeExp is not a string. Default value is "yyyy-MM-dd HH:mm:ss". See Datetime Patterns for valid date and time format patterns.

Examples:

> SELECT unix_timestamp();
 1476884637
> SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
 1460041200

Since: 1.5.0

weekday ¶

weekday(date) - Returns the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday).

Examples:

> SELECT weekday('2009-07-30');
 3

Since: 2.4.0

weekofyear ¶

weekofyear(date) - Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.

Examples:

> SELECT weekofyear('2008-02-20');
 8

Since: 1.5.0

window ¶

window(time_column, window_duration[, slide_duration[, start_time]]) - Bucketize rows into one or more time windows given a timestamp specifying column. Window starts are inclusive but the window ends are exclusive, e.g. 12:05 will be in the window [12:05,12:10) but not in [12:00,12:05). Windows can support microsecond precision. Windows in the order of months are not supported. See 'Window Operations on Event Time' in Structured Streaming guide doc for detailed explanation and examples.

Arguments:

time_column - The column or the expression to use as the timestamp for windowing by time. The time column must be of TimestampType.
window_duration - A string specifying the width of the window represented as "interval value". (See Interval Literal for more details.) Note that the duration is a fixed length of time, and does not vary over time according to a calendar.
slide_duration - A string specifying the sliding interval of the window represented as "interval value". A new window will be generated every slide_duration. Must be less than or equal to the window_duration. This duration is likewise absolute, and does not vary according to a calendar.
start_time - The offset with respect to 1970-01-01 00:00:00 UTC with which to start window intervals. For example, in order to have hourly tumbling windows that start 15 minutes past the hour, e.g. 12:15-13:15, 13:15-14:15... provide start_time as 15 minutes.

Examples:

> SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, start;
  A1    2021-01-01 00:00:00 2021-01-01 00:05:00 2
  A1    2021-01-01 00:05:00 2021-01-01 00:10:00 1
  A2    2021-01-01 00:00:00 2021-01-01 00:05:00 1
> SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '10 minutes', '5 minutes') ORDER BY a, start;
  A1    2020-12-31 23:55:00 2021-01-01 00:05:00 2
  A1    2021-01-01 00:00:00 2021-01-01 00:10:00 3
  A1    2021-01-01 00:05:00 2021-01-01 00:15:00 1
  A2    2020-12-31 23:55:00 2021-01-01 00:05:00 1
  A2    2021-01-01 00:00:00 2021-01-01 00:10:00 1

Since: 2.0.0

window_time ¶

window_time(window_column) - Extract the time value from time/session window column which can be used for event time value of window. The extracted time is (window.end - 1) which reflects the fact that the aggregating windows have exclusive upper bound - [start, end) See 'Window Operations on Event Time' in Structured Streaming guide doc for detailed explanation and examples.

Arguments:

window_column - The column representing time/session window.

Examples:

> SELECT a, window.start as start, window.end as end, window_time(window), cnt FROM (SELECT a, window, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, window.start);
  A1    2021-01-01 00:00:00 2021-01-01 00:05:00 2021-01-01 00:04:59.999999  2
  A1    2021-01-01 00:05:00 2021-01-01 00:10:00 2021-01-01 00:09:59.999999  1
  A2    2021-01-01 00:00:00 2021-01-01 00:05:00 2021-01-01 00:04:59.999999  1

Since: 3.4.0

year ¶

year(date) - Returns the year component of the date/timestamp.

Examples:

> SELECT year('2016-07-30');
 2016

Since: 1.5.0