PostgreSQL Timestamp

Introduction to PostgreSQL Timestamp

PostgreSQL uses the timestamp data type to store date and time format data in the database. Whenever a row is inserted or modified in the table, the timestamp is automatically updated to reflect the current date and time. If, in some cases, if time zone of the server changes, it will not affect on actual data that we have stored in the database. Timestamp data type storage size is 8 bytes for storing timestamps in the database; this data type is beneficial and important in PostgreSQL to store the date and time data in the database.

Syntax:

select now () :: timestamp;

select 'date' :: timestamp;

select 'date: time' :: timestamp;

select CURRENT_TIMESTAMP :: timestamp;

Below is the parameter description of the above syntax are as follows:

• Select: Select is used to select the timestamp value in timestamp syntax. We can select any date or time value to display the timestamp format in PostgreSQL. We can select the current date as now and the current timestamp function in PostgreSQL.
• Timestamp: It is used to store date and time format data in the database; it is used to handle or store the date and time of data stored in the database. We can select a date and time by using this data type.
• Now (): This function selects the current date and time in PostgreSQL. If we want to use the current date and time, we have used a now function in PostgreSQL. Now the function is more important to select the current date and time in PostgreSQL.
• Date and time: We can use any date and time to display the timestamp format. If we have used only the date, then it will display a date and time.
• Current timestamp: This function selects the current date and time in PostgreSQL. If we want to use the current date and time, we have to use a current timestamp function in PostgreSQL. The current timestamp function is more important to select the current date and time in PostgreSQL.

How does Timestamp Data Type work in PostgreSQL?

• We can use a timestamp data type with and without a time zone. If we want to use a without a time zone, we need to use a timestamp data type; if we’re going to use a time zone, we need to use a timestamp data type.
• Timestamp data type is divided into types with and without a time zone.
• Below is the function of the timestamp data type as follows.

1. Now ()
2. Current_timestamp
3. Timeofday ()

• Now the function selects the current date and time in PostgreSQL. If we want to use the current date and time, we need to use a now function in PostgreSQL. Now the function is more important to select the current date and time in PostgreSQL.

The below figure shows the example of now function in PostgreSQL:

Code:

select now();

Output:

• You can use the time of day function to select the current day, date, and time format, along with the corresponding timezone

The below figure shows an example of a time of day function in PostgreSQL.

Code:

select timeofday();

Output:

• The current timestamp function selects the current date and time in PostgreSQL. If we want to use the current date and time, we need to use a current timestamp function in PostgreSQL.

Code:

select Current_timestamp;

Output:

• The timestamp is not a time zone data type; it only stores time and date; in some cases, if the time zone of the server changes, it will not affect on actual data that we have stored in the database.
• Timestamp data storage size is 8 bytes to store data in the database; timestamp data type is beneficial and important in PostgreSQL to store the data’s date and time data.
• A low value of the timestamp data type is 4713 BC, and a higher value in PostgreSQL is 294276 AD.
• The timestamp data type storage size is 8 bytes.

The below example shows the size of the timestamp data type in PostgreSQL:

Code:

SELECT typname as "datatype", typlen as "length of datatype" FROM pg_type WHERE typname like 'timestamp%';

Output:

Examples

Given below are the examples:

Example #1

We are giving the data type of timestamp at the time of the creation of the table.

In the below example, we have given the timestamp format to the date of the joining column.

Code:

CREATE TABLE Employee_Timestamp (emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining timestamp NOT NULL);

Output:

Example #2

The below example shows adding a column and assigning a timestamp data type to the newly added column using alter command as follows.

Code:

ALTER TABLE Employee_Timestamp ADD COLUMN Date_Of_Resignation timestamp;

Output:

Example #3

In the below example, we have used date using timestamp format as follows. We are using with and without time zone format.

• With time zone: Below example shows the timestamp with the time zone in PostgreSQL.

Code:

select timestampz '2020-03-18';

Output:

• Without time zone: Below example shows the timestamp without a time zone in PostgreSQL as follows.

Code:

select timestamp '2020-03-18';

Output:

Conclusion

Timestamp uses 8 bytes of storage space for storing data in the database; timestamp data type is beneficial in PostgreSQL to store the date and time data in the database. There are three timestamp data types in PostgreSQL, i.e., now (), timeofday (), and Current_timestamp.

We hope that this EDUCBA information on “PostgreSQL Timestamp” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Date Functions
2. Date Function in PHP
3. PostgreSQL Table
4. Guide to PostgreSQL UNIQUE Constraint