PostgreSQL TIMESTAMP Data Type (With Examples)

PostgreSQL provides us with so many features which include a wide range of data types. The timestamp is a such datatype in PostgreSQL which is used most often for storing the DateTime values. In this tutorial, we will see detailed information about the timestamp datatype with examples. So, let’s get started!

Also Read: PostgreSQL Interval Data Type (With Examples)

Introduction to PostgreSQL TIMESTAMP Type

In PostgreSQL, timestamp simply means a date-time value. That means timestamp lets you store the date as well as time in a single column. Moreover, you can also store a time zone with the datetime value using the timestamp type. However, this is not its default behaviour.

Look at the table below to quickly understand the basic information about the timestamp type.

Here, p simply means the precision to specify the number of fractional digits in the second field. This can be a value from 0 to 6. Moreover, timestamp comes with another option called ‘with time zone‘. If you explicitly specify the ‘with time zone’, you will be able to store the time zone value as well.

PostgreSQL provides an extension to the ‘timestamp’ type which is a ‘timestamptz’. However, it is not an sql standard and just an abbriviation for ‘timestamp with time zone’.

Now let’s see the valid input formats for the timestamp type.

PostgreSQL TIMESTAMP Input Format

A concatenation of a date and time is a valid timestamp value, followed by the timezone (optional), followed by AD or BC (optional).

Example:

1999-01-08 04:05:06
1999-01-08 04:05:06 -8:00
January 8 04:05:06 1999 PST

Note that, ‘concatenation of date and time‘ means concatenation of any valid input formats of date and time is a valid timestamp.

Another essential thing to note here is that the SQL standard checks the presence of the + or – symbol in the timestamp value and differentiates the ‘timestamp with time zone‘ and ‘timestamp without time zone‘.

However, if you specify the + or – in the timestamp value but didn’t mention the ‘with time zone‘, the timestamp will be considered as a ‘without time zone‘.

Therefore, the two values given below are the exactly same, i.e., without the time zone.

TIMESTAMP '2004-10-19 10:23:54'
TIMESTAMP '2004-10-19 10:23:54+02'

To ensure that the value includes the time zone, explicitly mention the ‘with time zone’.

Example:

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

To read detailed information about the time zones, refer the section 8.5.3 time zones on the PostgreSQL official documentation.

Now let’s take some examples of the timestamp type.

PostgreSQL TIMESTAMP Examples

Let’s first create a table having a timestamp datatype column.

CREATE TABLE registrations(
id SERIAL PRIMARY KEY,
name VARCHAR(100),
registrationTime TIMESTAMP,
entryTime TIMESTAMP WITH TIME ZONE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created two columns. The ‘registrationTime‘ column is a timestamp ‘without time zone‘ whereas the ‘entrytime‘ is ‘with the zone‘.

But first, let’s check the current time zone.

SHOW timezone;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s now insert some values into it to test the input and outputs.

INSERT INTO registrations(name, registrationTime, entryTime)
VALUES
('John Smith', '2022-01-01 10:00:00-05', '2022-01-01 10:05:00-05'),
('Jane Doe', '2022-01-02 15:30:00+05', '2022-01-02 15:35:00+05');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have inserted two records. Both records consist of + and – values in both of the timestamp columns. Let’s check the output now.

As you can see, the registrationTime column values are the same but the entryTime column values are changed as it contains the time zone option.

Now let’s change the timezone and try fetching the same records. We will see some differences. Let’s see.

SET timezone = 'America/Los_Angeles';
SHOW timezone;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

SELECT * FROM registrations;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the registrationTime column values are still unchanged. However, the entryTime column values are changed as per the timezone.

This means if the column has the timestamp datatype having the ‘with time zone‘ option specified, the timestamp value changes depending on the current time zone that is set in your PostgreSQL server.

Conclusion

In this tutorial, we have learned about the timestamp datatype in PostgreSQL. Remember, the ‘timestamptz‘ and the ‘timestamp with time zone‘ are the same and can be used alternatively. The ‘timestamptz‘ is just a PostgreSQL extension to the standard SQL ‘timestamp‘ type. So, never get confused between these two!

Reference

PostgreSQL official documentation