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. This is the PostgreSQL SQL standard function which was used to return the values based on the start time of the current transactions in PostgreSQL. Current timestamp and transaction timestamp functions are equivalent to each other, but the current timestamp will return the current date and time and transaction timestamp will return transaction start time. Current timestamp, transaction timestamp and now function in PostgreSQL doing exactly the same but the current timestamp is 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 current timestamp is the SQL-Standard function in PostgreSQL used to return values based on the start time of the current transaction.
Precision: It is an optional parameter that was used in the current timestamp function in PostgreSQL.The precision is specified that number of digits in fractional second’s precision in the second filed of result in PostgreSQL. If we not used precision argument in current timestamp function it will return 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:
- The current timestamp is used to return the current date and time with the time zone in PostgreSQL.
- We can use the precision parameter with the current timestamp function in PostgreSQL.
- In PostgreSQL internally current timestamp will work as now function in PostgreSQL. The current timestamp and now function is similar work in PostgreSQL.
- Transaction timestamp and the current timestamp is equivalent to each other in PostgreSQL. But transaction timestamp will reflect the same which was return by the function.
Examples to Implement PostgreSQL CURRENT_TIMESTAMP()
The below example shows that now, current timestamp and transaction timestamp function is work similar 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 basically 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 very useful and important in PostgreSQL to return the date and timestamp with the time zone. We can provide the default value of the current timestamp to a column in PostgreSQL by using the default keyword. We can provide default current timestamp value to the column at the time of table creation. After specifying the default value as the current timestamp, then we have no need to insert current timestamp value in every insert statement. It will automatically take the current date and time in the column which has defined default current timestamp in PostgreSQL. The current timestamp function in PostgreSQL will return the current date in the following format are as follows.
‘YYYY-MM-DD HH:MM: SS.US+TZ’
Example #2
Below example shows the format of current timestamp function in PostgreSQL:
Code:
SELECT CURRENT_TIMESTAMP;
Output:
Explanation: In the above example the format of the current timestamp is as 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 precision parameter it will show a syntax error. The below example shows that we have no 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 precision value as 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 not used (), because we have not defined precision value in the below example.
Code:
SELECT CURRENT_TIMESTAMP;
Output:
Example #6
Use current timestamp in the column as the default value:
In the below example we have used the current timestamp as default value in the student table. In the below example we have created default current timestamp on 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 have no need to insert value in the column name as admission_date. It will take the automatic value as the current timestamp at the time of insertion are 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
This is a guide to PostgreSQL CURRENT_TIMESTAMP(). Here we discuss introduction to PostgreSQL CURRENT_TIMESTAMP(), syntax for it, how does it work and examples. You can also go through our other related articles to learn more –
