PostgreSQL: Understand TIMESTAMP vs TIMESTAMP WITH TIME ZONE

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a practical difference between TIMESTAMP and TIMESTAMP WITH TIME ZONE data type of PostgreSQL.

To understand the difference is a very important. Otherwise, it will affect your business or dataset.

I found many people are using TIMESTAMP WITH TIME ZONE data time, without knowing that this data type will change the time value according to different TIME ZONEs.

TIMESTAMP: Never change time basis on time zones
TIMESTAMP WITH TIME ZONE: Change the time basis on time zones

Please check the below demonstration:

Create two sample tables:

1 2	CREATE TABLE ABC (ID INT, MyTime TIMESTAMP); CREATE TABLE XYZ (ID INT, MyTime TIMESTAMP WITH TIME ZONE);

Insert CURRENT_TIMESTAMP:

1 2	INSERT INTO ABC VALUES(1, CURRENT_TIMESTAMP); INSERT INTO XYZ VALUES(1, CURRENT_TIMESTAMP);

Check the current timezone of your system:

1 2 3 4 5

SHOW timezone;   TimeZone --------------- Asia/Kolkata

Check the table data, which entered as your system time zone:

1 2 3 4 5

SELECT MyTime FROM ABC '2017-11-28 16:45:41.782017'   SELECT MyTime FROM XYZ; '2017-11-28 16:48:03.479841+05:30'

Now, change the timezone in your session:

1	SET timezone = 'US/Eastern';

Now, check the table data:
You can see, for ABC table no change and XYZ table changed as per the new time zone.

1 2 3 4 5

SELECT MyTime FROM ABC '2017-11-28 16:45:41.782017'   SELECT MyTime FROM XYZ; '2017-11-28 06:18:03.479841-05