Introduction to PostgreSQL timestamptz Data Type

The timestamptz (timestamp with time zone) data type in PostgreSQL is used to store timestamps with timezone information. Compared to the timestamp data type, it can store and convert timezone information. The timestamptz type converts the stored timestamp to UTC time and combines it with the stored timezone information for accurate conversion between different timezones.

Syntax

The syntax for the timestamptz data type is as follows:

TIMESTAMP WITH TIME ZONE

Use Cases

The timestamptz data type is typically used in applications that need to consider timezones, such as global applications and applications with multiple timezones. Using the timestamptz type ensures that timestamp information is accurately converted between different timezones. Additionally, since the timestamp type only supports timestamps in the local timezone, using the timestamptz type is essential in applications that need to consider multiple timezones.

Examples

Here are two examples of using the timestamptz data type.

Example 1

Assume we have a table named sales that contains sales records and sale dates. We can use the timestamptz data type to store the sale date and timestamp information. For example, we can create the table using the following command:

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date TIMESTAMPTZ NOT NULL,
  sale_amount DECIMAL(10,2) NOT NULL
);

Next, we can insert some sales records:

INSERT INTO sales (sale_date, sale_amount)
VALUES ('2023-03-13 14:30:00-07', 100.00),
       ('2023-03-13 19:45:00+03', 200.00),
       ('2023-03-14 09:15:00+01', 300.00);

We can use the following query to find sales records within a certain time range:

SELECT * FROM sales
WHERE sale_date BETWEEN '2023-03-13 00:00:00+00' AND '2023-03-14 00:00:00+00';

The output results are as follows:

 id |        sale_date          | sale_amount
----+---------------------------+-------------
  1 | 2023-03-13 21:30:00+00:00 |      100.00
  2 | 2023-03-13 16:45:00+00:00 |      200.00
  3 | 2023-03-14 08:15:00+00:00 |      300.00

From the above results, we can see that the stored sales timestamp information has been converted to UTC time and combined with the stored timezone information.

Example 2

Suppose we have a table named events that contains event records and event dates. We can use the timestamptz data type to store event dates and sort and filter them.

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name TEXT,
  event_date timestamptz
);

INSERT INTO events (name, event_date) VALUES
  ('Event A', '2022-01-15 12:30:00-05'),
  ('Event B', '2022-02-01 18:00:00-05'),
  ('Event C', '2022-03-12 09:45:00-05'),
  ('Event D', '2022-04-03 14:00:00-04');

Now we can query the table and order by event date:

SELECT name, event_date FROM events ORDER BY event_date;

The query result will be displayed in chronological order:

 name   |       event_date
---------+------------------------
 Event A | 2022-01-15 12:30:00-05
 Event B | 2022-02-01 18:00:00-05
 Event C | 2022-03-12 09:45:00-05
 Event D | 2022-04-03 14:00:00-04

We can also use the timestamptz data type to filter event records within a specific time range. For example, we can query for event records after February 1, 2022:

SELECT name, event_date FROM events WHERE event_date >= '2022-02-01';

The query result will only display event records after February 1, 2022:

 name   |       event_date
---------+------------------------
 Event B | 2022-02-01 18:00:00-05
 Event C | 2022-03-12 09:45:00-05
 Event D | 2022-04-03 14:00:00-04

Conclusion

In the above example, we demonstrated how to store and query data with timestamps using the timestamptz data type. This data type is well-suited for applications that require sorting and filtering of timestamps. Additionally, since it stores timezone information, it is also useful for operations across multiple timezones.