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:

My Personal Notes arrow_drop_up

Save