TIME

The TIME data type stores the time of day in UTC.

Aliases

In CockroachDB, the following are aliases:

TIME WITHOUT TIME ZONE

Syntax

A constant value of type TIME can be expressed using aninterpreted literal, or astring literalannotated withtype TIME orcoerced to typeTIME.

The string format for time is HH:MM:SS.SSSSSS. For example: TIME '05:40:00.000001'.

When it is unambiguous, a simple unannotated string literal can alsobe automatically interpreted as type TIME.

Note that the fractional portion of TIME is optional and is rounded to microseconds (i.e., six digits after the decimal) for compatibility with the PostgreSQL wire protocol.

Size

A TIME column supports values up to 8 bytes in width, but the total storage size is likely to be larger due to CockroachDB metadata.

Example

> CREATE TABLE time (time_id INT PRIMARY KEY, time_val TIME);

> SHOW COLUMNS FROM time;

+-------------+-----------+-------------+----------------+-----------------------+-------------+
| column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| time_id     | INT       |    false    | NULL           |                       | {"primary"} |
| time_val    | TIME      |    true     | NULL           |                       | {}          |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
(2 rows)

> INSERT INTO time VALUES (1, TIME '05:40:00'), (2, TIME '05:41:39');

> SELECT * FROM time;

+---------+---------------------------+
| time_id |         time_val          |
+---------+---------------------------+
|       1 | 0000-01-01 05:40:00+00:00 |
|       2 | 0000-01-01 05:41:39+00:00 |
+---------+---------------------------+
(2 rows)

Note:
The cockroach sql shell displays the date and time zone due to the Go SQL driver it uses. Other client drivers may behave similarly. In such cases, however, the date and time zone are not relevant and are not stored in the database.

Comparing TIME values:

> SELECT (SELECT time_val FROM time WHERE time_id = 1) < (SELECT time_val FROM time WHERE time_id = 2);

+--------------------------------+
|  (SELECT time_val FROM "time"  |
|  WHERE time_id = 1) < (SELECT  |
|   time_val FROM "time" WHERE   |
|          time_id = 2)          |
+--------------------------------+
|              true              |
+--------------------------------+
(1 row)

Supported casting & conversion

TIME values can be cast to any of the following data types:

See also

• Data Types
• SQL Feature Support

Was this page helpful?
YesNo