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 upon the time zone setting of the current database server the PostgreSQL NOW() function returns us the current date and time. We can adjust the result returned by the PostgreSQL NOW() function to other timezones as well. Also, we can get the current date and time without a timezone. This function can be used for giving default value to the column of the table.
Syntax:
NOW();
The return value of this function 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 of PostgreSQL NOW()
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 PostgreSQL NOW() function with 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 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 for giving default value to the columns of the table. We will create a table named ‘student’ by using the following CREATE table statement. In the ‘student’ table the column ‘created_date’ will contain the result returned by function PostgreSQL NOW() function.
Code:
CREATE TABLE student
(
stud_id serial PRIMARY KEY,
stud_name VARCHAR(80) NOT NULL,
created_date TIMESTAMPTZ DEFAULT Now()
);
Now we will insert some data in the ‘student’ table by using INSERT INTO statement and then we will verify the value of ‘created_date’ column, which will have the default value of current date and 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:
Now again, we will insert some data in the ‘student’ table by using INSERT INTO statement and then we will verify the value of ‘created_date’ column, which will have the default value of 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:
So, here we can see that whenever we insert the data in the table accordingly the ‘created_date’ column contains the default value of current date and time assigned while insertion.
Example #5
Now we will look at the some of the functions which are related to the NOW() function provided by the 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_TIMESTAMP
SELECT 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 timezone of the database server which makes it more useful for getting 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 change the PostgreSQL NOW() function’s result to other timezone’s as well.
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
This is a guide to PostgreSQL NOW(). Here we discuss the introduction, how does PostgreSQL NOW() function works and examples. You may also have a look at the following articles to learn more –
