Skip to content
Related Articles

Related Articles

PostgreSQL – Timestamp Data Type
  • Last Updated : 04 Jun, 2020

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 ytes 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 timestaptz 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 timestaptz 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:

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :