Open In App

PostgreSQL – Timestamp Data Type

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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: 
 

 


Last Updated : 24 Jan, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads