PostgreSQL: TimeStamp Data Type

PostgreSQL TIMESTAMP datatype allows you to store both DATE and TIME values. However, it does not store time zone data.

In PostgreSQL, the TIMESTAMP datatype is classified into two temporal data types

• TIMESTAMP: Datatype used to store time without a time zone.
• TIMESTAMPZ: Datatype used to store time with a time zone.

Storage of data in TIMESTAMPZ:

• In PostgreSQL TIMESTAMPZ store values in Universal Time Coordinated (UTC) format.
• When you insert data into TIMESTAMPZ column, PostgreSQL convert the inserted value into UTC format and store it in the column.
• When you query the TIMESTAMPZ column from a database, PostgreSQL converts the UTC value to the time value of the timezone set for a database server, the user, or the current database connection.

Now let's create timestamp_sample table containing timezone and timezonez datatypes to have a better understanding that how PostgreSQL handles them.

CREATE TABLE timestamp_sample (
    ts 	 TIMESTAMP, 
    tsz TIMESTAMPTZ
);

We can check the current timezone of the database using the following:

SHOW TIMEZONE;
Or 
SHOW TIME ZONE;

As you can see, the current time zone is set to Asia/Calcutta. Now let's insert data into timestamp_sample table and select the data.

INSERT INTO timestamp_sample (ts, tsz)
VALUES('2022-05-20 15:00:10-09','2022-05-20 15:00:10-09');

SELECT * FROM timestamp_sample;

As you can see, the timezone column has the same value as inserted, but timezonez has a value as per the current time zone defined. You can override the time zone to America/New_York, that is EST for this database connection using the following command

SET timezone = 'America/New_York';

Once the above command is executed, you can check the current timezone using the SHOW TIMEZONE command.

After that, run the SELECT query again to see data of timestamp_sample table.

Above, the timestampz column value changed again as per the new timestamp. It is always good practice to use timestampz datatype to store timestamp data.

PostgreSQL Timestamp functions

PostgreSQL provides some of the timestamp functions as below.

Get Current Timestamp with Time Zone

To get the current time value, PostgreSQL supports the now() function or CURRENT_TIMESTAMP function. Both of them shows timestamp with the time zone.

SELECT NOW(), CURRENT_TIMESTAMP;

Get Current Time with Time Zone

There is a function CURRENT_TIME that returns time with the time zone.

SELECT CURRENT_TIME;

Get Current Timestamp with Time Zone as String

Use the timeofday() function to get a timestamp with the time zone as string.

Convert between Time Zones

To convert a timestamp to another time zone, use the timezone(zone, timestamp) function. The timezone function takes two parameters, the zone is the time zone you want to convert and timestamp is the input timestamp value.

Let's check the current time zone first using SHOW TIMEZONE command.

Now, to convert current_timestamp to as per 'Asia/Calcutta' timezone, we can use the timezone() function, as shown below.

SELECT CURRENT_TIMESTAMP, timezone('Asia/Calcutta', CURRENT_TIMESTAMP);