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

# Timestamp with Time Zone

## Overview

Oxla provides you with two data types for handling timestamps:

1. [**Timestamp without Time Zone**](/sql-reference/sql-data-types/timestamp-without-time-zone): It allows you to store both date and time.

2. **Timestamp with Time Zone**: It stores date and time values but does not store time zone information within the database. Instead, it processes the time zone information during operations.
   1. During **INSERT** operation, the time zone is ignored. The date and time are stored without considering the time zone.
   2. During **the SELECT** operation, the time zone information from the user's session is also ignored. The data is returned exactly as it is stored without adjusting the time zone.

<Warning>**Important Note:** <br /> Keep in mind that all user sessions have a local timezone associated with them, affecting how timestamps `with time zone` values are displayed. <br /><br /> The timezone information **is not stored in the database**. Consequently, every time a user requests a value of this type, Oxla converts from UTC to the user's local timezone before displaying it.</Warning>
<Warning>**Important Note:** <br /> Oxla relies on timezone information served by host machine operating system. It must be up-to-date in order to ensure correct timestamp conversions, date calculations, and compliance with regional time changes such as daylight saving adjustments.</Warning>

On this page, you will learn about the timestamp with the time zone.

## Format

The `timestamp with time zone` data type has the following format:

```sql theme={null}
YYYY-MM-DD HH:MM:SS.SSSSSS+TZ
```

* `YYYY`: Four-digit year
* `MM`: One / two-digit month
* `DD`: One / two-digit day
* `HH`: One / two-digit hour (valid values from 00 to 23)
* `MM`: One / two-digit minutes (valid values from 00 to 59)
* `SS`: One / two-digit seconds (valid values from 00 to 59)
* `[.SSSSSS]`: Up to six fractional digits (microsecond precision)
* `+TZ`: Time zone offset in the format +/-HH:MM (e.g., +05:30, -08:00)

## Examples

### Case #1: Create a table

Let's create a table named `event_log` that consists of a timestamp without a time zone and a timestamp with time zone columns. The values in the `event_timestamp_tz` are in the “Europe/Moscow” timezone.

```sql theme={null}
CREATE TABLE events_log (
    event_name TEXT,
    event_timestamp TIMESTAMP WITHOUT TIME ZONE,
    event_timestamp_tz TIMESTAMP WITH TIME ZONE
);
INSERT INTO events_log (event_name, event_timestamp, event_timestamp_tz)
VALUES
    ('Event 1', '2023-07-27 12:30:00', '2023-07-27 12:30:00+03:00'),
    ('Event 2', '2023-07-27 08:45:00', '2023-07-27 08:45:00+03:00'),
    ('Event 3', '2023-07-27 20:15:00', '2023-07-27 20:15:00+03:00');
```

The table has been successfully created after executing the above query:

```sql theme={null}
COMPLETE
INSERT 0 3
```

### Case #2: Display the table

Run the `SELECT` statement to get all records of the table:

```sql theme={null}
SELECT event_timestamp, event_timestamp_tz
FROM events_log;
```

It will return the result as displayed below. We can see that the `event_timestamp_tz` is converted to UTC timezone.

```sql theme={null}
      event_timestamp       |       event_timestamp_tz        
----------------------------+---------------------------------
 2023-07-27 12:30:00.000000 | 2023-07-27 09:30:00.000000+0000
 2023-07-27 08:45:00.000000 | 2023-07-27 05:45:00.000000+0000
 2023-07-27 20:15:00.000000 | 2023-07-27 17:15:00.000000+0000
(3 rows)
```

### Case #3: Ordering Table by Timestamp

Let’s assume we want to sort the events based on the `event_timestamp` column and display the corresponding UTC in the `event_timestamp_tz` column. Run the following query:

```sql theme={null}
SELECT 
  event_timestamp, 
  event_timestamp_tz,
  event_timestamp AT TIME ZONE 'UTC' AS utc_time
FROM 
  events_log
ORDER BY 
  event_timestamp;
```

We’ll retrieve the `event_timestamp` and `event_timestamp_tz` columns and calculate the corresponding UTC time using the `AT TIME ZONE 'UTC'` operator.

We then order the results based on the `event_timestamp` column, giving us a sorted list of events with their corresponding local and UTC times.

```sql theme={null}
      event_timestamp       |       event_timestamp_tz        |            utc_time             
----------------------------+---------------------------------+---------------------------------
 2023-07-27 08:45:00.000000 | 2023-07-27 05:45:00.000000+0000 | 2023-07-27 08:45:00.000000+0000
 2023-07-27 12:30:00.000000 | 2023-07-27 09:30:00.000000+0000 | 2023-07-27 12:30:00.000000+0000
 2023-07-27 20:15:00.000000 | 2023-07-27 17:15:00.000000+0000 | 2023-07-27 20:15:00.000000+0000
(3 rows)
```

## AT TIME ZONE Operator

The `AT TIME ZONE` operator in timestamp with time zone converts the given timestamp with time zone to the new time zone, with no time zone designation.

**Syntax:**

```sql theme={null}
SELECT TIMESTAMP WITH TIME ZONE 'timestamp' AT TIME ZONE 'TIME_ZONE';
```

* `timestamp`: The date and time value with the time zone.

* `TIME_ZONE`: The target time zone to which the timestamp will be converted. The user's timezone is fixed to UTC.

**Example:**

In this example, we will convert a specified timestamp with time zone into the UTC timezone.

```sql theme={null}
SELECT TIMESTAMP WITH TIME ZONE '2023-03-04 10:29:90-05' AT TIME ZONE 'UTC';
```

The result will be a timestamp without a time zone.

```sql theme={null}
             f              
----------------------------
 2023-03-04 15:30:30.000000
(1 row)
```