> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# TIME / TIMETZ

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `TIME` <InternalLink path="data-types">data type</InternalLink> stores the time of day in UTC.

The `TIMETZ` data type stores a time of day with a time zone offset from UTC.

## Variants

`TIME` has two variants:

* `TIME`, which presents all `TIME` values in UTC.
* `TIMETZ`, which converts `TIME` values with a specified time zone offset from UTC.

  Ordering for `TIMETZ` is done in terms of [epoch](https://wikipedia.org/wiki/Epoch_\(computing\)). Time zones with lesser values are ranked higher if times are equal. For example, `'2:00-1' > '2:00+0'` and `'12:00-1' > '1:00+0'`.

  [Like PostgreSQL](https://www.postgresql.org/docs/current/static/datatype-datetime), we implement the `TIMETZ` variant for <InternalLink path="sql-feature-support">SQL standards compliance</InternalLink>. We also implement the `TIMETZ` variant for compatibility with ORMs, like <InternalLink path="build-a-java-app-with-cockroachdb-hibernate">Hibernate</InternalLink>.

You can use the <InternalLink path="functions-and-operators#date-and-time-functions">`timezone()`</InternalLink> and <InternalLink path="functions-and-operators#special-syntax-forms">`AT TIME ZONE`</InternalLink> functions to convert a `TIMETZ` into a `TIME` at a specified timezone, or to convert a `TIME` into a `TIMETZ` at a specified timezone.

<Tip>
  We recommend always using `TIME` instead of `TIMETZ`. Convert UTC values to the appropriate time zone on the client side.
</Tip>

## Aliases

In CockroachDB, the following are aliases:

| Alias    | Long Version             |
| -------- | ------------------------ |
| `TIME`   | `TIME WITHOUT TIME ZONE` |
| `TIMETZ` | `TIME WITH TIME ZONE`    |

## Syntax

### `TIME`

A constant value of type `TIME` can be expressed using an <InternalLink path="sql-constants#interpreted-literals">interpreted literal</InternalLink>, or a string literal <InternalLink path="scalar-expressions#explicitly-typed-expressions">annotated with</InternalLink> type `TIME` or <InternalLink path="scalar-expressions#explicit-type-coercions">coerced to</InternalLink> type `TIME`. When it is unambiguous, a simple unannotated <InternalLink path="sql-constants#string-literals">string literal</InternalLink> can also be automatically interpreted as type `TIME`.

The string format for `TIME` is `HH:MM:SS.SSSSSS`. For example: `TIME '05:40:00.000001'`. The fractional portion is optional and is rounded to microseconds (i.e., six digits after the decimal) for compatibility with the [PostgreSQL wire protocol](https://www.postgresql.org/docs/current/static/protocol).

<Note>
  A date of `0000-01-01` is displayed for all `TIME`/`TIMETZ` values, but is not stored in the database. To print without a date, you can cast the type to a `STRING`.

  A time zone offset of `+00:00` is also displayed for all `TIME` and <InternalLink path="timestamp">`TIMESTAMP`</InternalLink> values, but is not stored in the database.
</Note>

### `TIMETZ`

To express a `TIMETZ` value with a time zone offset from UTC, you can add an offset to a `TIME` value. For example, `TIMETZ '10:10:10.555555-05:00'` offsets from UTC by -5.

If no time zone is specified for a `TIMETZ` value, the `timezone` <InternalLink path="show-vars#supported-variables">session variable</InternalLink> is used. For example, if you <InternalLink path="set-vars#set-time-zone">set the `timezone`</InternalLink> for a session using `SET TIME ZONE -2`, and you define the `TIMETZ` as `TIMETZ '10:10:10.55'`, the value will be displayed with an offset of -2 from UTC.

`TIMETZ` is not affected by session-scoped offsets (unlike <InternalLink path="timestamp">`TIMESTAMPTZ`</InternalLink>). Time zone offsets only apply to values inserted after the offset has been set, and do not affect existing `TIMETZ` values, or `TIMETZ` values with a time zone offset specified.

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

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

## Precision

CockroachDB supports precision levels from 0 (seconds) to 6 (microseconds) for `TIME`/`TIMETZ` values. Precision in time values specifies the number of fractional digits retained in the seconds field. For example, specifying a `TIME` value as `TIME(3)` truncates the time precision to milliseconds. By default, `TIME`/`TIMETZ` values have a precision of 6 (microseconds).

You can use an <InternalLink path="alter-table#alter-column">`ALTER COLUMN... SET DATA TYPE`</InternalLink> statement to change the precision level of a `TIME`-typed column. If there is already a non-default precision level specified for the column, the precision level can only be changed to an equal or greater precision level. For an example, see [Create a table with a `TIME`-typed column, with precision](#create-a-table-with-a-time-typed-column-with-precision).

## Examples

### Create a table with a `TIME`-typed column

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE IF NOT EXISTS time (time_id INT PRIMARY KEY, time_val TIME);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM time;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  time_id     | INT8      |    false    | NULL           |                       | {primary} |   false
  time_val    | TIME      |    true     | NULL           |                       | {primary} |   false
(2 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO time VALUES (1, TIME '05:40:00'), (2, TIME '05:41:39');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM time;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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 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.
</Note>

Comparing `TIME` values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT (SELECT time_val FROM time WHERE time_id = 1) < (SELECT time_val FROM time WHERE time_id = 2);
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
< (SELECT time_val FROM time WHERE time_id = 2);
  ?column?
+----------+
    true
(1 row)
```

### Create a table with a `TIME`-typed column, with precision

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE IF NOT EXISTS time_precise (time_id INT PRIMARY KEY, time_val TIME(4));
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM time_precise;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  column_name | data_type | is_nullable | column_default | generation_expression |       indices       | is_hidden
--------------+-----------+-------------+----------------+-----------------------+---------------------+------------
  time_id     | INT8      |      f      | NULL           |                       | {time_precise_pkey} |     f
  time_val    | TIME(4)   |      t      | NULL           |                       | {time_precise_pkey} |     f
(2 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO time_precise VALUES (1, TIME '05:40:00.123456'), (2, TIME '05:41:39.12345');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM time_precise;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  time_id |   time_val
----------+----------------
        1 | 05:40:00.1235
        2 | 05:41:39.1235
(2 rows)
```

To change the precision level of a column, you can use an <InternalLink path="alter-table#alter-column">`ALTER COLUMN... SET DATA TYPE`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE time_precise ALTER COLUMN time_val SET DATA TYPE TIME(5);
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM time_precise;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  column_name | data_type | is_nullable | column_default | generation_expression |       indices       | is_hidden
--------------+-----------+-------------+----------------+-----------------------+---------------------+------------
  time_id     | INT8      |      f      | NULL           |                       | {time_precise_pkey} |     f
  time_val    | TIME(5)   |      t      | NULL           |                       | {time_precise_pkey} |     f
(2 rows)
```

You can reduce the precision of the values of `DECIMAL`, `TIMESTAMP`, and `TIMESTAMPTZ` columns; this yields the same behavior as the PostgreSQL implementation.

For example, to lower the precision of the `time_val` column, which is of type `TIME(5)`, use the following statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE time_precise ALTER COLUMN time_val SET DATA TYPE TIME(3);
```

## Supported casting & conversion

`TIME`/`TIMETZ` values can be <InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> to any of the following data types:

| Type       | Details                                                        |
| ---------- | -------------------------------------------------------------- |
| `INTERVAL` | Converts to the span of time since midnight (00:00)            |
| `STRING`   | Converts to format `'HH:MM:SS.SSSSSS'` (microsecond precision) |

<Note>
  CockroachDB displays `TIME '24:00:00'` and `TIMETZ '24:00:00'` as `0000-01-01 00:00:00`. To display the proper stored value (`24:00:00`), you can <InternalLink path="time">cast the data type to a `STRING`</InternalLink>.
</Note>

## See also

* <InternalLink path="data-types">Data Types</InternalLink>
* <InternalLink path="sql-feature-support">SQL Feature Support</InternalLink>