PostgreSQL – Timestamp Data Type
In PostgreSQL 2 temporal data types namely timestamp and timestamptz where one is without timezone and the later is with timezone respectively, are supported to store Time and Date to a column. Both timestamp and timestamptz uses 8 bytes for storing timestamp values.
Syntax: TIMESTAMP; or TIMESTAMPTZ;
Now let’s look into some example for better understanding.
Example 1:
First we create a table that has both timestamp and timestamptz columns using the below command:
CREATE TABLE timestamp_demo (ts TIMESTAMP, tstz TIMESTAMPTZ);
Then we will set the time zone of database server to Asia/Calcutta as below:
SET timezone = 'Asia/Calcutta';
Now that our time zone is set, we will insert a new row into the timestamp_demo table using the below command:
INSERT INTO timestamp_demo (ts, tstz) VALUES ( '2020-06-22 19:10:25-07', '2020-06-22 19:10:25-07' );
Now we will query data from the timestamp and timestamptz columns using the below command:
SELECT ts, tstz FROM timestamp_demo;
Output:
Example 2:
In this example we will convert Asia/Calcutta timezone into America/New_York timezone using the timezone(zone, timestamp) function.
First we create a table that has both timestamp and timestamptz columns using the below command:
CREATE TABLE timezone_conversion_demo ( tstz TIMESTAMPTZ);
Then we will set the time zone of database server to Asia/Calcutta as below:
SET timezone = 'Asia/Calcutta';
Now that our time zone is set, we will insert a new row into the timezone_conversion_demo table using the below command:
INSERT INTO timezone_conversion_demo ( tstz) VALUES ( '2020-06-22 19:10:25-07' );
Now we will query data from the timestamp and timestamptz columns using the below command:
SELECT timezone('America/New_York', '2020-06-22 19:10:25');
Output:
Contact Us