JIYIK CN >

This article will discuss the timestamp types in PostgreSQL and show how they differ.

Timestamps in PostgreSQL

In PostgreSQL, there are two types of timestamps.

1. Timestamp without time zone
2. Timestamp with time zone

The first stores the local date. For example, suppose it is now 11.00 on a 24-hour system clock.

So this will be stored as 11.00. If it is saved in a database, say a remote database, and someone pulls this row from the CST time zone, he will still see it as 11.00.

However, this is not the real time. To view the time you need to convert to the CST time zone.

Since it does not store any information about the time zone, it cannot be further determined in different time zones.

Second approach solves this problem. So whenever you push a timestamp in database it will extract the timezone from host system and save the timezone along with the timestamp.

Assume we are in GMT+6 time zone. Here is a demonstration of timestamp.

SELECT now() as "System Time",
now()::timestamp as "postgres Time",
now() AT TIME ZONE 'GMT' as "time without zone",
now() AT TIME ZONE 'CST' as "time without zone",
now()::timestamp at TIME ZONE 'GMT' as "Timestamp GMT",
now()::timestamp at TIME ZONE 'CST' as "Timestamp CST" ;

Here, the first column contains the system time and the second column contains the timestamp after converting the time to a timestamp without time zone.

Output:

          System Time          |       postgres Time        |     time without zone      |     time without zone      |         Timestamp GMT         |         Timestamp CST
-------------------------------+----------------------------+----------------------------+----------------------------+-------------------------------+-------------------------------
 2022-03-15 10:19:05.432758+06 | 2022-03-15 10:19:05.432758 | 2022-03-15 04:19:05.432758 | 2022-03-14 22:19:05.432758 | 2022-03-15 16:19:05.432758+06 | 2022-03-15 22:19:05.432758+06
(1 row)

Difference between timestamps with and without time zone in PostgreSQL

无时区Let's create a table and see how it can store timestamps and how to use them in PostgreSQL 有时区.

First, connect your psql console to Postgres and run the following SQL commands to create the following tables:

CREATE TABLE Times(
    id INT PRIMARY KEY NOT NULL,
    time_without_zone TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
    time_with_zone TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Now, populate the table with some entries.

INSERT INTO Times(id) VALUES(1);
INSERT INTO Times(id) VALUES(2);
INSERT INTO Times(id) VALUES(3);
INSERT INTO Times(id) VALUES(4);
INSERT INTO Times(id) VALUES(5);
INSERT INTO Times(id) VALUES(6);
INSERT INTO Times(id) VALUES(7);

Output:

postgres=# select * from times;
 id |     time_without_zone      |        time_with_zone
----+----------------------------+-------------------------------
  1 | 2022-03-15 10:29:03.52078  | 2022-03-15 10:29:03.52078+06
  2 | 2022-03-15 10:29:03.52564  | 2022-03-15 10:29:03.52564+06
  3 | 2022-03-15 10:29:03.526723 | 2022-03-15 10:29:03.526723+06
  4 | 2022-03-15 10:29:03.527775 | 2022-03-15 10:29:03.527775+06
  5 | 2022-03-15 10:29:03.528865 | 2022-03-15 10:29:03.528865+06
  6 | 2022-03-15 10:29:03.529941 | 2022-03-15 10:29:03.529941+06
  7 | 2022-03-15 10:29:05.045774 | 2022-03-15 10:29:05.045774+06
(7 rows)

postgres=#

As you can see, time_with_zonethe column stores the time with the GMT+06 time zone. The time can be converted to any other time zone because psql will know the cardinality of the time in the column.

Here is a sample output that shows what happens if you try to insert a timestamp with a time zone on a column that does not have a time zone type.

INSERT INTO Times(id,time_without_zone,time_with_zone) VALUES(9,'2022-03-15 10:29:05.045774+06','2022-03-15 10:29:05.045774+06');

Output:

postgres=# select * from times where id=9;
 id |     time_without_zone      |        time_with_zone
----+----------------------------+-------------------------------
  9 | 2022-03-15 10:29:05.045774 | 2022-03-15 10:29:05.045774+06
(1 row)

As you can see, psql simply time_without_zonedropped the +06 in the column.

If you want to see all the time zones available in Postgres, you can run the following SQL command:

postgres=# SELECT name FROM pg_timezone_names;
               name
----------------------------------
 Africa/Abidjan
 Africa/Accra
 Africa/Addis_Ababa
 Africa/Algiers
 Africa/Asmara
 Africa/Asmera
 Africa/Bamako
 Africa/Bangui
 Africa/Banjul
 Africa/Bissau
 -- More --

Here, the system is running in the GMT+6 time zone. If you want to change the Postgres time zone to another time zone, you can run the following command:

postgres=# SET TIMEZONE='UTC';
SET

If you see the data in the table above, you will see that the column with the time zone has been successfully converted to UTC.

postgres=# select * from times;
 id |     time_without_zone      |        time_with_zone
----+----------------------------+-------------------------------
  1 | 2022-03-15 10:29:03.52078  | 2022-03-15 04:29:03.52078+00
  2 | 2022-03-15 10:29:03.52564  | 2022-03-15 04:29:03.52564+00
  3 | 2022-03-15 10:29:03.526723 | 2022-03-15 04:29:03.526723+00
  4 | 2022-03-15 10:29:03.527775 | 2022-03-15 04:29:03.527775+00
  5 | 2022-03-15 10:29:03.528865 | 2022-03-15 04:29:03.528865+00
  6 | 2022-03-15 10:29:03.529941 | 2022-03-15 04:29:03.529941+00
  7 | 2022-03-15 10:29:05.045774 | 2022-03-15 04:29:05.045774+00
  9 | 2022-03-15 10:29:05.045774 | 2022-03-15 04:29:05.045774+00
(8 rows)

You can see time_without_zonethat remains the same, but time_with_zoneconverted from GMT+06 to UTC. To learn more about timestamp formats and representations, visit here .