Updated May 29, 2023
Introduction to PostgreSQL CURRENT_TIMESTAMP()
PostgreSQL CURRENT_TIMESTAMP() is used to return the current date and time with time zone, it will display the time when our transaction starts. The PostgreSQL SQL standard function ‘current_timestamp’ returns values based on the start time of the current transaction. It retrieves the start time of the current transaction and provides the corresponding timestamp information. It retrieves the current transaction’s start time and provides the corresponding timestamp information. Current timestamp and transaction timestamp functions are equivalent, but the current timestamp will return the current date and time, and the transaction timestamp will return the transaction start time. Current timestamp, transaction timestamp, and now function in PostgreSQL are doing the same, but the current timestamp is a syntactical oddity function.
Syntax
Below is the syntax of the current_timestamp function in PostgreSQL.
Current_timestamp;OR
Current_timestamp (<Precision>)Parameter Description
Below is the parameter description syntax of the current timestamp function in PostgreSQL.
Current timestamp: Current timestamp in PostgreSQL will return the current date and time. The SQL-Standard function in PostgreSQL that returns values based on the start time of the current transaction is ‘current_timestamp’. It retrieves the current date and time, including the time zone, at the beginning of the ongoing transaction.
Precision: The current timestamp function in PostgreSQL allows an optional parameter to specify the precision of fractional seconds in the result. The precision determines the number of digits in the fractional seconds portion of the timestamp. If we do not use the precision argument in the current timestamp function, it will return a timestamp with time zone this includes include the full fractional second’s precision in PostgreSQL.
How does PostgreSQL CURRENT_TIMESTAMP() function work?
Below is the working of the current timestamp in PostgreSQL:
- PostgreSQL utilizes the current timestamp to retrieve the current date and time, including the time zone. It provides the current timestamp information in PostgreSQL.
- We can use the precision parameter with the current timestamp function in PostgreSQL.
- In PostgreSQL, the internally current timestamp will work as now function in PostgreSQL. The current timestamp and now function is similar to work in PostgreSQL.
- Transaction timestamp and the current timestamp is equivalent to each other in PostgreSQL. But the transaction timestamp will reflect the same, which the function returned.
Examples to Implement PostgreSQL CURRENT_TIMESTAMP()
The below example shows that now, the current timestamp and transaction timestamp function work similarly to each other:
Example #1
Code:
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT transaction_timestamp();Output:
Explanation: The above example shows the time and timestamp of all three functions working is the same. The current timestamp is used as the default timestamp value of a column in PostgreSQL. The current timestamp and the current time are to deliver the values with the time zone in PostgreSQL. The start time of the current statement is the time of the latest command received from the client. The current timestamp is useful and important in PostgreSQL to return the date and timestamp with the time zone. In PostgreSQL, we can assign the default value of the current timestamp to a column using the “default” keyword during table creation. This eliminates the need to manually insert the current timestamp value in every insert statement, as the current timestamp function will automatically populate the column with the current date and time in a specified format.
‘YYYY-MM-DD HH:MM: SS.US+TZ’
Example #2
The below example shows the format of the current timestamp function in PostgreSQL:
Code:
SELECT CURRENT_TIMESTAMP;Output:
Explanation: In the above example, the current timestamp format is below.
- YYYY – It is defined as the year in four-digit.
- MM – It defines the month in two digits. The range is 01 to 12.
- DD – It is defined as today’s date in two digits. The range is 01 to 31.
- HH – It is defined as the current hour in two digits. The range is 00 to 23.
- MM – It is defined as the current minute in two digits. The range is 00 to 59.
- SS – It is defined as the current second in two digits. The range is 00 to 59.
- TZ – It is defined as the time zone in the current timestamp function.
- We have not need to use () in the current timestamp function when we have not used the precision parameter.
Example #3
If we have used () without a precision parameter, it will show a syntax error. The below example shows that we do not need to use () when we have not used the precision parameter:
Code:
SELECT CURRENT_TIMESTAMP;Output:
Example #4
Current timestamp with precision value:
In the below example, we have used a precision value of 5 with the current timestamp. We have used five fractional seconds to define the current timestamp.
Code:
SELECT CURRENT_TIMESTAMP (5);Output:
Example #5
Current timestamp without using precision value:
In the below example, we have not used precision value with the current timestamp. We did not use () because we have not defined the precision value in the example below.
Code:
SELECT CURRENT_TIMESTAMP;Output:
Example #6
Use the current timestamp in the column as the default value:
In the below example, we have used the current timestamp as the default value in the student table. In the below example, we have created the default current timestamp on the admission_date column.
Code:
CREATE TABLE student (stud_id serial PRIMARY KEY, stud_address varchar(255) NOT NULL, stud_name varchar(255) NOT NULL, admission_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);Output:
Example #7
After creating the student table, we do not need to insert a value in the column name as admission_date. It will take the automatic value as the current timestamp at the time of insertion as follows.
Code:
INSERT INTO STUDENT (stud_id, stud_address, stud_name) VALUES (1, 'Pune', 'ABC');
INSERT INTO STUDENT (stud_id, stud_address, stud_name) VALUES (2, 'Mumbai', 'PQR');
select * from STUDENT;Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL CURRENT_TIMESTAMP()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
