Overview
PostgreSQL provides two time-based data types, TIMESTAMP and TIMESTAMPTZ, which are often a source of confusion. TIMESTAMP stores a date and time pair without timezone information, while TIMESTAMPTZ includes time zone data for timezone-aware applications.
Understanding TIMESTAMP
Let’s start by understanding the basic TIMESTAMP data type. In PostgreSQL, TIMESTAMP is a data type that stores date and time values without any information about time zones. It’s essentially a ‘timestamp without time zone’. It assumes that the stored time value is in the same time zone that the database server is operating in, whatever that may be.
Example 1: Creating a Table with a TIMESTAMP Field
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_name VARCHAR(100),
event_time TIMESTAMP
);
This example creates a table named ‘events’ with an id field, an event_name field, and an event_time field that records the timestamp of the event.
Example 2: Inserting Data into TIMESTAMP Field
INSERT INTO events (event_name, event_time)
VALUES ('Product Launch', '2023-04-15 14:30:00');
This statement inserts a new row into the ‘events’ table, with a specific timestamp for the ‘Product Launch’ event.
Dealing With Time Zones in TIMESTAMP
An essential aspect of dealing with TIMESTAMP data is considering how the absence of time zone information can affect the interpretation of the stored dates and times.
Example 3: Retrieving TIMESTAMP Data
SELECT event_name, event_time
FROM events;
Running this query will display the ‘event_time’ exactly as it was inserted without any conversion for time zones.
Understanding TIMESTAMPTZ
TIMESTAMPTZ is short for ‘timestamp with time zone’. This data type stores the timestamp along with information about the time zone. PostgreSQL does behind the scenes adjustments to convert stored values to Coordinated Universal Time (UTC). When retrieved, these timestamps are converted back to the appropriate time zone of the client, if that time zone is set.
Example 4: Creating a Table with a TIMESTAMPTZ Field
CREATE TO events_tz (
id SERIAL PRIMARY KEY,
event_name VARCHAR(100),
event_time TIMESTAMPTZ
);
This SQL statement creates a similar ‘events’ table, but this time the ‘event_time’ field is a TIMESTAMPTZ field, meaning that it will store time zone information alongside the timestamp.
Example 5: Inserting Data into TIMESTAMPTZ Field
INSERT INTO events_tz (event_name, event_time)
VALUES ('Global Webinar', '2023-04-15 14:30:00+00');
In this example, the ‘+00’ indicates that the ‘Global Webinar’ event time is set to UTC. PostgreSQL will store this time internally in UTC.
Working with Client Time Zones in TIMESTAMPTZ
The real utility of TIMESTAMPTZ emerges when we recognize that users from different time zones might interact with our database.
Example 6: Retrieving Data in Different Time Zones
-- Setting the time zone
SET TIME ZONE 'Asia/Kolkata';
-- Retrievring event time in set time zone
SELECT event_name, event_time
FROM events_tz;
This retrieval converts the UTC time stored in the database to ‘Asia/Kolkata’ time zone before presenting it to the user.
Conversions Between TIMESTAMP and TIMESTAMPTZ
Conversion functions allow you to switch from one type to the other, adapting your data to the needs of your application.
Example 7: Converting TIMESTAMP to TIMESTAMPTZ
SELECT event_name, event_time AT TIME ZONE 'UTC'
FROM events;
This query takes the ‘event_time’ which is a TIMESTAMP, and displays it as if it were a TIMESTAMPTZ in UTC time.
Conclusion.
So far, we have discussed the use of TIMESTAMP and TIMESTAMPTZ in everyday scenarios. Understanding the proper use of time data types in PostgreSQL is essential for dealing with temporal data correctly. This tutorial provides a comprehensive explanation of the TIMESTAMP and TIMESTAMPTZ data types, including how they handle time zones and how they can affect your applications when dealing with time-based data. At this point, you should be able to use these data types more confidently and correctly in your PostgreSQL database.