Date and time functions

Every Spark SQL date and timestamp function on Databasin Sail: whether it is supported, how it differs from Apache Spark, and examples with results.

Last updated October 20, 2026
Reading time 36 min read

This page lists every Spark SQL date and timestamp function: 81 work on Databasin Sail as in Apache Spark and 1 works with a difference noted below. Each function shows its status on Sail, its usage, and examples whose results on Sail match Spark's.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

Date and timestamp

add_months · convert_timezone · curdate · current_date · current_time · current_timestamp · current_timezone · date_add · date_diff · date_format · date_from_unix_date · date_part · date_sub · date_trunc · dateadd · datediff · datepart · day · dayname · dayofmonth · dayofweek · dayofyear · extract · from_unixtime · from_utc_timestamp · hour · last_day · localtimestamp · make_date · make_dt_interval · make_interval · make_time · make_timestamp · make_timestamp_ltz · make_timestamp_ntz · make_ym_interval · minute · month · monthname · months_between · next_day · now · quarter · second · session_window · time_bucket · time_diff · time_from_micros · time_from_millis · time_from_seconds · time_to_micros · time_to_millis · time_to_seconds · time_trunc · timestamp_micros · timestamp_millis · timestamp_seconds · to_date · to_time · to_timestamp · to_timestamp_ltz · to_timestamp_ntz · to_unix_timestamp · to_utc_timestamp · trunc · try_make_interval · try_make_timestamp · try_make_timestamp_ltz · try_make_timestamp_ntz · try_to_date · try_to_time · try_to_timestamp · unix_date · unix_micros · unix_millis · unix_seconds · unix_timestamp · weekday · weekofyear · window · window_time · year

add_months

Supported.

add_months(start_date, num_months): Returns the date that is num_months after start_date.

Since Spark 1.5.0.

SELECT add_months('2016-08-31', 1);
-- 2016-09-30

convert_timezone

Supported.

convert_timezone([sourceTz, ]targetTz, sourceTs): Converts the timestamp without time zone sourceTs from the sourceTz time zone to targetTz.

  • sourceTz: the time zone for the input timestamp. If it is missed, the current session time zone is used as the source time zone.
  • targetTz: the time zone to which the input timestamp should be converted
  • sourceTs: a timestamp without time zone

Since Spark 3.4.0.

SELECT convert_timezone('Europe/Brussels', 'America/Los_Angeles', timestamp_ntz'2021-12-06 00:00:00');
-- 2021-12-05 15:00:00

SELECT convert_timezone('Europe/Brussels', timestamp_ntz'2021-12-05 15:00:00');
-- 2021-12-06 00:00:00

curdate

Supported.

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

Since Spark 3.4.0.

SELECT curdate();
-- for example: 2022-09-06

current_date

Supported.

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.

Note from Spark: The syntax without braces has been supported since 2.0.1.

Since Spark 1.5.0.

SELECT current_date();
-- for example: 2020-04-25

SELECT current_date;
-- for example: 2020-04-25

current_time

Supported, differs from Spark. New in Spark 4.1.0. Only current_time() works: it returns TIME(9) (Spark: TIME(6)); current_time without parentheses and current_time(precision) fail.

current_time([precision]): Returns the current time at the start of query evaluation. All calls of current_time within the same query return the same value.

current_time: Returns the current time at the start of query evaluation.

  • precision: An optional integer literal in the range [0..6], indicating how many fractional digits of seconds to include. If omitted, the default is 6.
SELECT current_time();
-- for example: 15:49:11.914120

current_timestamp

Supported.

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.

Note from Spark: The syntax without braces has been supported since 2.0.1.

Since Spark 1.5.0.

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

SELECT current_timestamp;
-- for example: 2020-04-25 15:49:11.914

current_timezone

Supported.

current_timezone(): Returns the current session local timezone.

Since Spark 3.1.0.

SELECT current_timezone();
-- Asia/Shanghai

date_add

Supported.

date_add(start_date, num_days): Returns the date that is num_days after start_date.

Since Spark 1.5.0.

SELECT date_add('2016-07-30', 1);
-- 2016-07-31

date_diff

Supported.

date_diff(endDate, startDate): Returns the number of days from startDate to endDate.

Since Spark 3.4.0.

SELECT date_diff('2009-07-31', '2009-07-30');
-- 1

SELECT date_diff('2009-07-30', '2009-07-31');
-- -1

date_format

Supported.

date_format(timestamp, fmt): Converts timestamp to a value of string in the format specified by the date format fmt.

  • timestamp: A date/timestamp or string to be converted to the given format.
  • fmt: Date/time format pattern to follow. See Datetime Patterns for valid date and time format patterns.

Since Spark 1.5.0.

SELECT date_format('2016-04-08', 'y');
-- 2016

date_from_unix_date

Supported.

date_from_unix_date(days): Create date from the number of days since 1970-01-01.

Since Spark 3.1.0.

SELECT date_from_unix_date(1);
-- 1970-01-02

date_part

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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

  • 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

Note from Spark: The _FUNC_ function is equivalent to the SQL-standard function EXTRACT(field FROM source)

Since Spark 3.0.0.

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

date_sub

Supported.

date_sub(start_date, num_days): Returns the date that is num_days before start_date.

Since Spark 1.5.0.

SELECT date_sub('2016-07-30', 1);
-- 2016-07-29

date_trunc

Supported.

date_trunc(fmt, ts): Returns timestamp ts truncated to the unit specified by the format model fmt.

  • fmt: the format representing the unit to be truncated to - "YEAR", "YYYY", "YY" - truncate to the first date of the year that the ts falls in, the time part will be zero out - "QUARTER" - truncate to the first date of the quarter that the ts falls in, the time part will be zero out - "MONTH", "MM", "MON" - truncate to the first date of the month that the ts falls in, the time part will be zero out - "WEEK" - truncate to the Monday of the week that the ts falls in, the time part will be zero out - "DAY", "DD" - zero out the time part - "HOUR" - zero out the minute and second with fraction part - "MINUTE"- zero out the second with fraction part - "SECOND" - zero out the second fraction part - "MILLISECOND" - zero out the microseconds - "MICROSECOND" - everything remains
  • ts: datetime value or valid timestamp string

Since Spark 2.3.0.

SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
-- 2015-01-01 00:00:00

SELECT date_trunc('MM', '2015-03-05T09:32:05.359');
-- 2015-03-01 00:00:00

SELECT date_trunc('DD', '2015-03-05T09:32:05.359');
-- 2015-03-05 00:00:00

SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359');
-- 2015-03-05 09:00:00

SELECT date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456');
-- 2015-03-05 09:32:05.123

dateadd

Supported.

dateadd(start_date, num_days): Returns the date that is num_days after start_date.

Since Spark 3.4.0.

SELECT dateadd('2016-07-30', 1);
-- 2016-07-31

datediff

Supported.

datediff(endDate, startDate): Returns the number of days from startDate to endDate.

Since Spark 1.5.0.

SELECT datediff('2009-07-31', '2009-07-30');
-- 1

SELECT datediff('2009-07-30', '2009-07-31');
-- -1

datepart

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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

  • 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

Note from Spark: The _FUNC_ function is equivalent to the SQL-standard function EXTRACT(field FROM source)

Since Spark 3.4.0.

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

day

Supported.

day(date): Returns the day of month of the date/timestamp.

Since Spark 1.5.0.

SELECT day('2009-07-30');
-- 30

dayname

Supported.

dayname(date): Returns the three-letter abbreviated day name from the given date.

Since Spark 4.0.0.

SELECT dayname(DATE('2008-02-20'));
-- Wed

dayofmonth

Supported.

dayofmonth(date): Returns the day of month of the date/timestamp.

Since Spark 1.5.0.

SELECT dayofmonth('2009-07-30');
-- 30

dayofweek

Supported.

dayofweek(date): Returns the day of the week for date/timestamp (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

Since Spark 2.3.0.

SELECT dayofweek('2009-07-30');
-- 5

dayofyear

Supported.

dayofyear(date): Returns the day of year of the date/timestamp.

Since Spark 1.5.0.

SELECT dayofyear('2016-04-09');
-- 100

extract

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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

  • 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

Note from Spark: The _FUNC_ function is equivalent to date_part(field, source).

Since Spark 3.0.0.

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

from_unixtime

Supported.

from_unixtime(unix_time[, fmt]): Returns unix_time in the specified fmt.

  • unix_time: UNIX Timestamp to be converted to the provided format.
  • fmt: Date/time format pattern to follow. See Datetime Patterns for valid date and time format patterns. The 'yyyy-MM-dd HH:mm:ss' pattern is used if omitted.

Since Spark 1.5.0.

SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
-- 1969-12-31 16:00:00

SELECT from_unixtime(0);
-- 1969-12-31 16:00:00

from_utc_timestamp

Supported.

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

Since Spark 1.5.0.

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

hour

Supported.

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.

Since Spark 1.5.0.

SELECT hour('2018-02-14 12:58:59');
-- 12

SELECT hour(TIME'13:59:59.999999');
-- 13

last_day

Supported.

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

Since Spark 1.5.0.

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

localtimestamp

Supported.

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.

Since Spark 3.4.0.

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

make_date

Supported.

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.

  • 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

Since Spark 3.0.0.

SELECT make_date(2013, 7, 15);
-- 2013-07-15

SELECT make_date(2019, 7, NULL);
-- NULL

make_dt_interval

Supported.

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

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

Since Spark 3.2.0.

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

make_interval

Supported.

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

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

Since Spark 3.0.0.

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

make_time

Supported. New in Spark 4.1.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • 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
SELECT make_time(6, 30, 45.887);
-- 06:30:45.887

SELECT make_time(NULL, 30, 0);
-- NULL

make_timestamp

Supported.

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.

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

Since Spark 3.0.0.

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

make_timestamp_ltz

Supported.

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.

  • 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

Since Spark 3.4.0.

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

make_timestamp_ntz

Supported.

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.

  • 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

Since Spark 3.4.0.

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

make_ym_interval

Supported.

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

  • years: the number of years, positive or negative
  • months: the number of months, positive or negative

Since Spark 3.2.0.

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

minute

Supported.

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.

Since Spark 1.5.0.

SELECT minute('2009-07-30 12:58:59');
-- 58

SELECT minute(TIME'23:59:59.999999');
-- 59

month

Supported.

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

Since Spark 1.5.0.

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

monthname

Supported.

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

Since Spark 4.0.0.

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

months_between

Supported.

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.

Since Spark 1.5.0.

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

next_day

Supported.

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.

Since Spark 1.5.0.

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

now

Supported.

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

Since Spark 1.6.0.

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

quarter

Supported.

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

Since Spark 1.5.0.

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

second

Supported.

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.

Since Spark 1.5.0.

SELECT second('2018-02-14 12:58:59');
-- 59

SELECT second(TIME'13:25:59.999999');
-- 59

session_window

Supported.

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.

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

Since Spark 3.2.0.

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

time_bucket

Supported. New in Spark 4.2.0. Results match Spark; the auto-generated result column name can differ in some examples. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

time_bucket(bucketSize, ts[, origin]): Returns the start of the bucket that ts falls into, where buckets are defined by the given bucketSize interval aligned to optional origin. For TIMESTAMP_NTZ, bucketing is performed in UTC. For TIMESTAMP, year-month interval buckets and calendar-day components of day-time interval buckets align to the session time zone.

  • bucketSize: A day-time or year-month interval defining the bucket size. Must be positive and foldable.
  • ts: A TIMESTAMP or TIMESTAMP_NTZ value to bucket.
  • origin: Optional TIMESTAMP or TIMESTAMP_NTZ alignment anchor. Defaults to 1970-01-01 00:00:00. Must be the same type as ts and must be foldable.
SELECT time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-01 11:27:00', TIMESTAMP '1970-01-01 00:00:00');
-- 2024-01-01 11:15:00

SELECT time_bucket(INTERVAL '1' HOUR, TIMESTAMP '2024-01-01 11:27:00');
-- 2024-01-01 11:00:00

SELECT time_bucket(INTERVAL '1' MONTH, TIMESTAMP '2024-07-20 14:30:00', TIMESTAMP '2024-06-15 09:00:00');
-- 2024-07-15 09:00:00

time_diff

Supported. New in Spark 4.1.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • 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
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

time_from_micros

Supported. New in Spark 4.2.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • micros: microseconds since midnight (0 to 86399999999)
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

time_from_millis

Supported. New in Spark 4.2.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • millis: milliseconds since midnight (0 to 86399999)
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

time_from_seconds

Supported. New in Spark 4.2.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • seconds: seconds since midnight (0 to 86399.999999). Supports decimals for fractional seconds.
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

time_to_micros

Supported. New in Spark 4.2.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • time: TIME value to convert
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

time_to_millis

Supported. New in Spark 4.2.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • time: TIME value to convert
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

time_to_seconds

Supported. New in Spark 4.2.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • time: TIME value to convert
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

time_trunc

Supported. New in Spark 4.1.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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

  • 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
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

timestamp_micros

Supported.

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

Since Spark 3.1.0.

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

timestamp_millis

Supported.

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

Since Spark 3.1.0.

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

timestamp_seconds

Supported.

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

Since Spark 3.1.0.

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

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

to_date

Supported.

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.

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

Since Spark 1.5.0.

SELECT to_date('2009-07-30 04:17:52');
-- 2009-07-30

SELECT to_date('2016-12-31', 'yyyy-MM-dd');
-- 2016-12-31

to_time

Supported. New in Spark 4.1.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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.

  • str: A string to be parsed to time.
  • format: Time format pattern to follow. See Datetime Patterns for valid time format patterns.
SELECT to_time('00:12:00');
-- 00:12:00

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

to_timestamp

Supported.

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.

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

Since Spark 2.2.0.

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

to_timestamp_ltz

Supported.

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.

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

Since Spark 3.4.0.

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

to_timestamp_ntz

Supported.

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.

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

Since Spark 3.4.0.

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

to_unix_timestamp

Supported.

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

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

Since Spark 1.6.0.

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

to_utc_timestamp

Supported.

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

Since Spark 1.5.0.

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

trunc

Supported.

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

  • 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

Since Spark 1.5.0.

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

try_make_interval

Supported.

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.

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

Since Spark 4.0.0.

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

try_make_timestamp

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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.

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

Since Spark 4.0.0.

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

try_make_timestamp_ltz

Supported.

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.

  • 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

Since Spark 4.0.0.

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

try_make_timestamp_ntz

Supported.

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.

  • 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

Since Spark 4.0.0.

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

try_to_date

Supported. New in Spark 4.0.0.

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.

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

try_to_time

Supported. New in Spark 4.1.0. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

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.

  • str: A string to be parsed to time.
  • format: Time format pattern to follow. See Datetime Patterns for valid time format patterns.
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

try_to_timestamp

Supported.

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.

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

Since Spark 3.4.0.

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

unix_date

Supported.

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

Since Spark 3.1.0.

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

unix_micros

Supported.

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

Since Spark 3.1.0.

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

unix_millis

Supported.

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

Since Spark 3.1.0.

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

unix_seconds

Supported.

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

Since Spark 3.1.0.

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

unix_timestamp

Supported.

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

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

Since Spark 1.5.0.

SELECT unix_timestamp();
-- for example: 1476884637

SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
-- for example: 1460041200

weekday

Supported.

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

Since Spark 2.4.0.

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

weekofyear

Supported.

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.

Since Spark 1.5.0.

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

window

Supported.

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.

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

Since Spark 2.0.0.

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

window_time

Supported.

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.

  • window_column: The column representing time/session window.

Since Spark 3.4.0.

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

year

Supported.

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

Since Spark 1.5.0.

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

Function names, usage text, argument descriptions and examples are from the Apache Spark SQL built-in functions documentation, Copyright The Apache Software Foundation, licensed under the Apache License, Version 2.0. Apache Spark is a trademark of the Apache Software Foundation. Sail status and notes are from Databasin.

Where to go next

Sail SQL referenceStatements, and every function page. Spark compatibilityWhere Sail differs from Apache Spark. Aggregate functionsMore functions. Window, generator and table functionsMore functions.