Timestamp with Time Zone

​

Overview

Oxla provides you with two data types for handling timestamps:

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

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:

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.

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:

COMPLETE
INSERT 0 3

​

Case #2: Display the table

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

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.

      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:

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.

      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:

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.

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.

             f              
----------------------------
 2023-03-04 15:30:30.000000
(1 row)

​