date_trunc function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns timestamp truncated to the unit specified in unit.

Syntax

date_trunc(unit, expr)

Arguments

  • unit: A STRING literal.
  • expr: A DATE, TIMESTAMP, or STRING with a valid timestamp format.

Returns

A TIMESTAMP.

Notes

Valid units for unit are (case-insensitive):

  • 'YEAR', 'YYYY', 'YY': truncate to the first date of the year that the expr falls in, the time part will be zero out
  • 'QUARTER': truncate to the first date of the quarter that the expr falls in, the time part will be zero out
  • 'MONTH', 'MM', 'MON': truncate to the first date of the month that the expr falls in, the time part will be zero out
  • 'WEEK': truncate to the Monday of the week that the expr 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

If the unit is not well-formed the function returns NULL.

Examples

> 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

-- Not a recognised unit
> SELECT date_trunc('MS', '2015-03-05T09:32:05.123456');
 NULL