Updated May 6, 2023
Introduction to PostgreSQL NOW()
We can get the current date and time by using the PostgreSQL NOW() Function. The PostgreSQL NOW() Function’s return type is the timestamp with the time zone. Depending on the current database server’s time zone setting, the PostgreSQL NOW() Function returns the current date and time. We can also adjust the result of the PostgreSQL NOW() Function returned to other timezones. Also, we can get the current date and time without a timezone. This Function can be used to give the default value to the table’s column.
Syntax:
NOW();This Function’s return value is the current date and time value as per the timezone setting of the current database server.
How does PostgreSQL NOW() Function works?
Here we see how the NOW() function returns the current date and time, which depend upon the timezone setting of the current database server’s timezone.
Syntax:
Now()Example:
Code:
Select Now();Output:
Examples
Given below are the examples:
Example #1
Now we will change the timezone to ‘America/Chicago’ and then will use the PostgreSQL NOW() Function as follows:
Code:
set timezone = 'America/Chicago';
select now()Output:
Illustrate the result of the above statement by using the following snapshot.
Example #2
We can get the current date and time without the timezone by using this Function with a timestamp defined explicitly as follows:
Code:
SELECT NOW()::timestamp;Output:
Illustrate the result of the above statement by using the following snapshot.
Example #3
We can use the PostgreSQL NOW() Function with the date and time operators.
a. Like consider the following example where we will try to get the result as 5 hours from now:
Code:
SELECT (NOW() + interval '5 hour') AS extra_five_hour;Output:
Illustrate the result of the above statement by using the following snapshot.
b. We can add an extra day to the current time as well as follows:
Code:
SELECT (NOW() + interval '2 day') AS this_time_after_two_days;Output:
Illustrate the result of the above statement by using the following snapshot.
c. Also, we can get the previous time as well by using the minus (-) operator with time as follows:
Code:
SELECT now() - interval '3 hours 15 minutes' AS three_hours_15_min_go;Output:
Illustrate the result of the above statement by using the following snapshot.
Example #4
We can use the PostgreSQL NOW() Function to give the table’s columns a default value. Using the CREATE table statement, we will create a table named ‘student’. The column ‘created_date’ will contain the result returned by the Function PostgreSQL NOW() Function in the student’s table.
Code:
CREATE TABLE student
(
stud_id serial PRIMARY KEY,
stud_name VARCHAR(80) NOT NULL,
created_date TIMESTAMPTZ DEFAULT Now()
);We will then insert some data in the ‘student’ table using the INSERT INTO statement, and then we will verify the value of the ‘created_date’ column, which will have the default value of the current date time assigned while insertion.
Code:
INSERT INTO student(stud_name)
VALUES ('Jacob'), ('David');Illustrate the result of the above statement by using the following snapshot and SQL statement.
Code:
select * from student;Output:
Again, we will insert some data in the ‘student’ table using the INSERT INTO statement. Then we will verify the value of the ‘created_date’ column, which will have the default value of the current date and time assigned while insertion.
Code:
INSERT INTO student(stud_name)
VALUES ('John'), ('Robert')Illustrate the result of the above statement by using the following snapshot and SQL statement.
Code:
select * from student;Output:
Here we can see that whenever we insert the data in the table accordingly, the ‘created_date’ column contains the default value of the current date and time assigned during insertion.
Example #5
Now we will look at some of the functions which are related to the NOW() Function provided by PostgreSQL.
a. We can use different functions to get the current date and time without timezone as follows:
Code:
LOCALTIME or LOCALTIMESTAMP
SELECT LOCALTIME, LOCALTIMESTAMP;Output:
Illustrate the result of the above statement by using the following snapshot.
b. We can use different functions to get the current date and time with timezone as follows:
Code:
CURRENT_TIME or CURRENT_TIMESTAMPSELECT CURRENT_TIME, CURRENT_TIMESTAMP;Output:
Illustrate the result of the above statement by using the following snapshot.
Advantages
Below are the advantages:
- We can use the NOW() Function provided by PostgreSQL to get the current date and time.
- The PostgreSQL NOW() Function operates along with the database server’s timezone, which makes it more useful for getting the local time and date of the database.
- We can provide the default value to the columns of the table by using the Function.
- We can also change this Function’s result to other time zones.
Conclusion
Here we saw how to use the PostgreSQL NOW() Function and how the PostgreSQL NOW() Function works. Also, we have added several examples to understand.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL NOW()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
