Difference Between Timestamp With and Without Time Zone in PostgreSQL
This tutorial will discuss the types of timestamps in PostgreSQL and demonstrate their differences.
Timestamp in PostgreSQL
In PostgreSQL, there are two types of timestamps.
- Timestamp without time zone
- Timestamp with time zone
The first one stores the local date. For example, suppose it is now 11.00 in a 24H system clock.
So, this will be stored as 11.00. If this is saved in the database, say in the remote database, and someone is pulling this row from the CST time zone, he will still see it as 11.00.
However, it wasn’t the actual time. The CST needed to see that time is converted to the CST time zone.
As it doesn’t store any information regarding the time zone, it can’t be determined further in different time zones.
This problem is solved in the second method. So, whenever you push a timestamp in the database, it will pull the time zone from the host system and save the time zone with the timestamp.
Suppose we are in the GMT+6 time zone. Here’s a demonstration of the 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 the timestamp without a 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 Timestamp With and Without Time Zone in PostgreSQL
Let’s create a table and see how it stores the timestamps and how you’ll use the without time zone and with time zone in PostgreSQL.
First, connect your psql console to Postgres and then run the following SQL command to create a table like the following:
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, the column time_with_zone stores the time with the GMT+06 time zone. The time can be converted to any other time zone as psql will know the base for the time in the column.
Here’s a sample output that shows what will happen if you try to insert a timestamp with a time zone on a column without 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 just dropped the +06 in the time_without_zone column.
If you want to see all the available time zone 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 your Postgres time zone to a different one, you can run the following command:
postgres=# SET TIMEZONE='UTC';
SET
If you see the above tables’ data, you will see that the column with time zone is successfully converted to the 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 that time_without_zone remains the same, but the time_with_zone is converted from GMT+06 to UTC. To know more about timestamp formats and representation, visit the official documentation.
