Skip to main content
Last updated on

TIME type

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18.3 and above

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

Represents a time of day comprising values of fields hour, minute, and second, without a time-zone.

Syntax​

TIME | TIME(p)

p: An optional precision specifying the number of fractional digits in the seconds field. Valid values are 0 to 6. If p is omitted, the default precision is 6 (microseconds).

Limits​

The range of time values supported is 00:00:00.000000 to 23:59:59.999999.

Literals​

TIME timeString

timeString
{ '[h]h:[m]m' |
  '[h]h:[m]m:[s]s' |
  '[h]h:[m]m:[s]s.[f...]' |
  '[h]h:[m]m AM|PM' |
  '[h]h:[m]m:[s]s AM|PM' |
  '[h]h:[m]m:[s]s.[f...] AM|PM' }
  • [h]h: A one or two digit hour between 00 and 23 (24-hour format) or between 01 and 12 (12-hour format with AM/PM).
  • [m]m: A one or two digit minute between 00 and 59.
  • [s]s: A one or two digit second between 00 and 59.
  • [f...]: Up to 6 digits of fractional seconds.
  • AM or PM: An optional case-insensitive suffix indicating a 12-hour clock value.

The prefix TIME is case insensitive.

If the second component is not specified it defaults to 0.

If the literal does not represent a proper time Databricks raises an error.

Precision rules​

When two TIME values with different precisions are combined (for example, in COALESCE, CASE, UNION, or arithmetic), the result uses the wider of the two precisions.

For example, the least common type of TIME(3) and TIME(6) is TIME(6).

When you cast a TIME value to a lower precision, the fractional seconds are truncated, not rounded:

SQL
> SELECT CAST(TIME'12:30:45.987654' AS TIME(3));
  12:30:45.987

When you add or subtract a day-time interval to a TIME, the result uses the wider of the TIME precision and the interval's fractional second precision. For example, TIME(0) + INTERVAL '1.123456' SECOND returns a TIME(6). If the result falls outside [00:00:00, 24:00:00), Databricks raises DATETIME_OVERFLOW.

Notes​

All operations on TIME values are performed without taking any time zone into account.

To use TIME columns in Delta Lake tables, support is enabled automatically when you create a new Delta table with a column of TIME type. To add a TIME column to an existing table, you must first enable the feature:

SQL
ALTER TABLE table_name SET TBLPROPERTIES ('delta.feature.timeType' = 'supported')

The TIME type has the following limitations:

  • Not supported in generated columns.
  • Not supported for bloom filter indexes.
  • Not supported for Z-Ordering.

Examples​

SQL
> SELECT TIME'10:30:00';
  10:30:00

> SELECT TIME'08:15';
  08:15:00

> SELECT TIME'23:59:59.123456';
  23:59:59.123456

> SELECT CAST('14:30:00' AS TIME);
  14:30:00

> SELECT CAST('09:15:30.5' AS TIME(3));
  09:15:30.500

> SELECT current_time();
  16:57:04.304361

> SELECT make_time(6, 30, 45.887);
  06:30:45.887000
On this page