Understanding PostgreSQL Timestamp Data Types

Summary: in this tutorial, you will learn about the PostgreSQL timestamp data types including timestamp and timestamptz. You will also learn how to use some handy functions to handle timestamp data more effectively.

Introduction to PostgreSQL timestamp

PostgreSQL provides you with two temporal data types for handling timestamp:

timestamp: a timestamp without timezone one.
timestamptz: timestamp with a timezone.

The timestamp datatype allows you to store both date and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.

The timestamptz datatype is the timestamp with the time zone. The timestamptz datatype is a time zone-aware date and time data type.

PostgreSQL stores the timestamptz in UTC value.

When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
When you query timestamptz from the database, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection.

Notice that both timestamp and timestamptz uses 8 bytes for storing the timestamp values as shown in the following query:

SELECT
      typname,
      typlen
FROM
      pg_type
WHERE
      typname ~ '^timestamp';
Code language: SQL (Structured Query Language) (sql)

   typname   | typlen
-------------+--------
 timestamp   |      8
 timestamptz |      8
(2 rows)
Code language: SQL (Structured Query Language) (sql)

It’s important to note that timestamptz value is stored as a UTC value. PostgreSQL does not store any timezone data with the timestamptz value.

PostgreSQL timestamp example

Let’s take a look at an example of using the timestamp and timestamptz to have a better understanding of how PostgreSQL handles them.

First, create a table that consists of both timestamp the timestamptz columns.

CREATE TABLE timestamp_demo (
    ts TIMESTAMP, 
    tstz TIMESTAMPTZ
);
Code language: SQL (Structured Query Language) (sql)

Next, set the time zone of the database server to America/Los_Angeles.

SET timezone = 'America/Los_Angeles';
Code language: SQL (Structured Query Language) (sql)

By the way, you can see the current time zone using the SHOW TIMEZONE command:

SHOW TIMEZONE;
Code language: SQL (Structured Query Language) (sql)

      TimeZone
---------------------
 America/Los_Angeles
(1 row)
Code language: SQL (Structured Query Language) (sql)

Then, insert a new row into the timstamp_demo table:

INSERT INTO timestamp_demo (ts, tstz)
VALUES('2016-06-22 19:10:25-07','2016-06-22 19:10:25-07');
Code language: SQL (Structured Query Language) (sql)

After that, query data from the timestamp and timestamptz columns.

SELECT 
   ts, tstz
FROM 
   timestamp_demo;
Code language: SQL (Structured Query Language) (sql)

         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)
Code language: SQL (Structured Query Language) (sql)

The query returns the same timestamp values as the inserted values.

Finally, change the timezone of the current session to America/New_York and query data again.

SET timezone = 'America/New_York';
Code language: SQL (Structured Query Language) (sql)

SELECT
	ts,
	tstz
FROM
	timestamp_demo;
Code language: SQL (Structured Query Language) (sql)

         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)
Code language: SQL (Structured Query Language) (sql)

The value in the timestamp column does not change, whereas the value in the timestamptz column is adjusted to the new time zone of 'America/New_York'.

Generally, it is a good practice to use the timestamptz datatype to store the timestamp data.

PostgreSQL timestamp functions

To handle timestamp data effectively, PostgreSQL provides some handy functions as follows:

Getting the current time

To get the current timestamp you use the NOW() function as follows:

SELECT NOW();
Code language: SQL (Structured Query Language) (sql)

              now
-------------------------------
 2016-06-22 20:44:52.134125-07
(1 row)
Code language: SQL (Structured Query Language) (sql)

Or you can use the CURRENT_TIMESTAMP:

SELECT CURRENT_TIMESTAMP;
Code language: SQL (Structured Query Language) (sql)

To get the current time without date, you use CURRENT_TIME:

       timetz
--------------------
 20:49:04.566025-07
(1 row)
Code language: SQL (Structured Query Language) (sql)

Note that both CURRENT_TIMESTAMP and CURRENT_TIME return the current time with the time zone.

To get the time of day in the string format, you use the timeofday() function.

SELECT TIMEOFDAY();
Code language: SQL (Structured Query Language) (sql)

              timeofday
-------------------------------------
 Wed Jun 22 20:51:12.632420 2016 PDT
Code language: SQL (Structured Query Language) (sql)

Convert between timezones

To convert a timestamp to another time zone, you use the timezone(zone, timestamp) function.

SHOW TIMEZONE;
Code language: SQL (Structured Query Language) (sql)

      TimeZone
---------------------
 America/Los_Angeles
(1 row)
Code language: SQL (Structured Query Language) (sql)

The current timezone is America/Los_Angeles.

To convert 2016-06-01 00:00 to America/New_York timezone, you use the timezone() function as follows:

SELECT timezone('America/New_York','2016-06-01 00:00');
Code language: SQL (Structured Query Language) (sql)

      timezone
---------------------
 2016-06-01 03:00:00
(1 row)
Code language: SQL (Structured Query Language) (sql)

Note that we pass the timestamp as a string to the timezone() function, PostgreSQL casts it to timestamptz implicitly. It is better to cast a timestamp value to the timestamptz data type explicitly as the following statement:

SELECT timezone('America/New_York','2016-06-01 00:00'::timestamptz);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have introduced you to the PostgreSQL timestamp data type and shown you how to use some useful functions for manipulating timestamp values.

Was this tutorial helpful ?