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' |
