Timestamp

In this section, we are going to understand the working of the PostgreSQL Timestamp data type. And we also see examples of the Timestamp data type, and we also see some accessible timestamp functions like NOW(), CURRENT_TIMESTAMP, CURRENT_TIME, TIMEOFDAY(), and timezone(zone, timestamp), which help us to handle time values more efficiently.

What is PostgreSQL Timestamp Data Type?

In PostgreSQL, the next data type is TIMESTAMP, which can store both TIME and DATE values. But it does not support any time zone data. And it implies that when we convert the time zone of our database server, the timestamp value will be stored in the database and cannot be modified repeatedly.

In PostgreSQL, the TIMESTAMP data type classified into two temporal data types, which are as follows:

• timestamp
• timpstamptz

Timestamp: The timestamp data type is used without time zone one.

Timestamptz: The timestamptz data type is used with a time zone.

Note:

• The timestamptz datatype is a time zone-related date and time data type, and it is the timestamp with the time zone.
• The timestamp and timestamptz stores the 8 bytes of storage of the timestamp values as we can see in the following command:

SELECT typname, typlen  

FROM pg_type  

WHERE typname ~ '^timestamp';

Output

After exciting the above command, we will get the below output, where we can see that both timestamp and timestamptz datatype stores 8 bytes of timestamp values.

In PostgreSQL, the timestamptz data type stores in UTC value:

• If we insert a value into a timestamptz column, the PostgreSQL changes the timestamptz value into a UTC value and stores the UTC value in the table.
• PostgreSQL changes the UTC value back to the time zone established by the database server, the current database connection or the user if we request timestamptz from the database.
• And PostgreSQL does not contain any timezone data with the timestamptz

Syntax of PostgreSQL Timestamp data type

The syntax of PostgreSQL Timestamp data types is as follows:

TIMESTAMP;   

OR

TIMESTAMPTZ;  

Example of PostgreSQL TIMESTAMP data type

Let us see one sample examples to understand how the PostgreSQL timestamp and timestamptz data type works.

We are creating one new table as ts_demo, which contains timestamp and timestamptz data types with the CREATE command’s help and inserting some values using the INSERT command.

To create a ts_demo into a Javatpoint database, we use the CREATE command.

The ts_demo table contains the two columns such as ts1 TIMESTAMP and ts2 TIMESTAMPTZ, as shown in the following command:

 CREATE TABLE ts_demo (  

    ts1 TIMESTAMP,   

    ts2 TIMESTAMPTZ  

); 

Output

On executing the above command, we will get the following message: the ts_demo table has been created successfully.

When the ts_demo table is created successfully, we will set the time zone of the database server to Australia/Darwin as we can see in the following command:

SET TIMEZONE ='Australia/Darwin';  

Output

After implementing the above command, we will get the below message window, which displays that the time zone has been set successfully.

If we want to see the current time zone, we can use the following command, as shown below:

SHOW TIMEZONE;  

Output

After executing the above command, we will get the following output:

After setting a time zone as Australia/Darwin successfully, we will insert some values into it with the INSERT command’s help.

  INSERT INTO ts_demo (ts1, ts2)  

VALUES('2020-10-05 14:01:10-08',  

       '2020-10-05 14:01:10-08');  

Output

After implementing the above command, we will get the following message window, which displays that the particular values have been inserted successfully into the ts_demo table.

After creating and inserting the ts_demo table’s values, we will use the SELECT command to return all rows of the ts_demo table:

SELECT ts1, ts2  

FROM ts_demo; 

Output

After successfully implementing the above command, we will get the below output, which displays the timestamp value present in the ts_demo table:

After that, we will again modify the current session’s time zone to Asia/Calcutta, as shown in the following command.

SET TIMEZONE = 'Asia/Calcutta';  

Output

On implementing the above command, we will get the below message window which displays that the new time zone has been set successfully.

Then, we will use the SELECT command to see the existing data in the ts_demo table:

SELECT ts1, ts2  

FROM ts_demo; 

Output

After successfully executing the above command, we will retrieve the below result:

As we can see, both the outputs value’s in the timestamp column does not modify, however the value in the timestamptz column can be changed based on the new time zone of ‘Asia/Calcutta’.

Note: Usually, we will use the timestamptz datatype to store the timestamp data values as it is always a good exercise.

PostgreSQL timestamp functions

We have the following Timestamp functions such as NOW(), CURRENT_TIMESTAMP, CURRENT_TIME, TIMEOFDAY(), converting time value into a different time zone using the timezone(zone, timestamp) function on time values available in the PostgreSQL, which help us to enhance the performance while we are using the timestamp data type.

Let us see different examples to understand how PostgreSQL timestamp functions work.

Getting the current time

We can use the NOW() function to retrieve the current timestamp.

To return the current timestamp of the database server, we can use the below command:

SELECT NOW();  

Output

After implementing the above command, we will get the current timestamp with the help of the NOW() function in the output, as shown below:

Or we can use the CURRENT_TIMESTAMP function as well to get the current timestamp with time zone:

SELECT CURRENT_TIMESTAMP;  

Output

After implementing the above command, we will get the current timestamp with time zone using the CURRENT_TIMESTAMP function in the output, as shown below:

And we can use the CURRENT_TIME function, which helps us retrieve the current time without a date.

Note: The CURRENT_TIMESTAMP and CURRENT_TIME both the functions are used to return the current time with the time zone.

In PostgreSQL, we can also retrieve the time of day in the string format with the help of the timeofday() function, as shown in the below command:

SELECT TIMEOFDAY();  

Output

After successfully executing the above command, we will get the time of day in the string format using the TIMEOFDAY() function in the output, as shown below:

Modification between timezones

We can use the timezone(zone, timestamp) function to change the timestamp into another time zone.

The below syntax is used to change the time into a different time zone:

SELECT timezone(zone, timestamp) ;  

Firstly, we will see the current time zone with the help of the following command, as shown below:

SHOW TIMEZONE;  

Output

After executing the above command, we will get the below output, which displays that the current time zone is Asia/Calcutta in the database server:

Let us see a sample example for our better understanding.

Now, we are using the timezone() function to modify the current timezone as 2020-10-06 00:00 to Australia/Sydney timezone, as shown in the following command:

SELECT timezone('Australia/Sydney','2020-10-06 02:14');  

Output

We will get the following output on implementing the above command, which displays the modified time based on the Australia/Sydney timezone:

Note: PostgreSQL performs the timestamptz indirectly if we provide the timestamp as a string to the timezone() function.

To cast a timestamp value to the timestamptz data type directly as it is always an excellent approach, as shown in the below command:

SELECT timezone('America/New_York','2016-06-01 00:00'::timestamptz);  

Output

We will get the below output after successfully executing the above command, which displays the converted time without time with the timestamptz data type.

Overview

In the PostgreSQL Timestamp data type section, we have learned the following topics:

• The PostgreSQL Timestamp data type is used to store the time and date values for a specified column.
• We used different TIMESTAMP functions, for example, NOW(), CURRENT_TIMESTAMP, CURRENT_TIME, TIMEOFDAY(), and timezone(zone, timestamp) to enhance and handle the TIME and DATE value from the particular table.
• We can change the Timestamp value into a different time zone with the timezone(zone, timestamp) function.