PostgreSQL provides built-in functions for getting the current DateTime with the time zone, such as NOW() and CURRENT_TIMESTAMP. The NOW() function doesn’t accept any argument; however, the CURRENT_TIMESTAMP function can accept an optional argument named “precision”. Both these functions retrieve today’s DateTime with a timezone offset.
This post demonstrates various examples to illustrate the usage of the NOW() function and CURRENT_TIMESTAMP function in Postgres.
How to Get Current DateTime(With Timezone) Using Postgres NOW() Function?
It is a built-in DateTime function that retrieves today’s DateTime with a timezone offset. You must use the stated with a set of parenthesis, as shown below:
NOW();
Its return type is TIMESTAMPTZ(TIMESTAMP with TIMEZONE).
Example 1: How to Get Today’s DateTime Via NOW()?
To get the current date and time with timezone offset, you must use the NOW() function with the collaboration of the Postgres SELECT query:
SELECT NOW();
Example 2: How to Insert Current DateTime With Timezone into a Specific Table?
We have created a sample table named staff_info, whose structure is described in the following snippet:
Let’s learn how to insert the Current DateTime with timezone to a table using the NOW() function:
INSERT INTO staff_info(staff_id, staff_name, joining_date, contract_duration) VALUES (15, 'Anna', NOW(), '2 Years');
Let’s check out the newly inserted record using the below-provided command:
SELECT * FROM staff_info;
The current date-time and the timezone offset have been inserted into the “staff_info” table.
Example 3: How to Set NOW() Function as the Column’s Default Value?
Use the ALTER TABLE command with ALTER COLUMN and SET DEFAULT clause followed by the NOW() function to set the current date, time, and time zone offset as the default value of a targeted column:
ALTER TABLE staff_info ALTER COLUMN joining_date SET DEFAULT NOW();
Describe the “staff_info” table to see its structure:
\d staff_info;
The output snippet clearly states that the “NOW()” function has been set as the default value for the “joining_date” column. Let’s insert a new record into the “staff_info” table:
INSERT INTO staff_info(staff_id, staff_name, staff_designation, contract_duration) VALUES (12, 'Henry', 'Author', '2 Years');
The below snippet shows the newly inserted record:
SELECT * FROM staff_info;
How to Get Current DateTime(With Timezone) via Postgres CURRENT_TIMESTAMP Function?
The CURRENT_TIMESTAMP function may or may not accept a precision parameter, which determines the fractional points for the seconds' field.
CURRENT_TIMESTAMP(precision);
Skipping the precision parameter will retrieve the seconds with full available precision. The return type of the stated function is “TIMESTAMPTZ(TIMESTAMP WITH TIME ZONE)”.
Example 1: How to Get Today’s DateTime Via CURRENT_TIMESTAMP?
A SELECT command can be used to get the current date and time with the timezone using the CURRENT_TIMESTAMP function.
SELECT CURRENT_TIMESTAMP;
Example 2: How to Find Current DateTime(with timezone) With Specific Precision?
You can specify the precision value as an argument to the CURRENT_TIMESTAMP() function, as shown in the following snippet:
SELECT CURRENT_TIMESTAMP(2);
The output signifies the results for the CURRENT_TIMESTAMP function with and without precision parameters.
Note: You can use the CURRENT_TIMESTAMP function as a column’s default value.
Conclusion
In Postgres, the NOW() and CURRENT_TIMESTAMP functions are used to fetch the current date and time with the time zone offset. The return type of these functions is “TIMESTAMP with TIMEZONE” or “TIMESTAMPTZ”. The NOW() function doesn’t accept any argument; however, the CURRENT_TIMESTAMP function can accept an optional argument named “precision”, which determines the fractional points for the seconds' field. This blog considered various examples to show the usage of Postgres’ NOW() and CURRENT_TIMESTAMP functions.
