PostgreSQL: TimeStamp Data Type
TIMESTAMP datatype allows you to store both
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:
TIMESTAMPZstore values in Universal Time Coordinated (UTC) format.
When you insert data into
TIMESTAMPZcolumn, PostgreSQL convert the inserted value into UTC format and store it in the column.
When you query the
TIMESTAMPZcolumn 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
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
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.
Get Current Timestamp with Time Zone as String
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);