Timestamp with Time Zone
Overview
Oxla provides you with two data types for handling timestamps:
-
Timestamp without Time Zone: It allows you to store both date and time.
-
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.
-
During INSERT operation, the time zone is ignored. The date and time are stored without considering the time zone.
-
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.
-
Keep in mind that all user sessions have a local timezone associated with them, affecting how timestamps
with time zone
values are displayed. 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.
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)