PostgreSQL Now() Function – A 101 Guide

Using a Relational Database, you can view the current (or any) date and/or time of your server’s time zone or any other time zone according to your requirements. PostgreSQL, being one of the strongest Relational Database Management Systems also supports this feature. There are multiple ways to do this, one of which is the Now() function.

This article will help you comprehensively understand the PostgreSQL Now() Function and its need. You will also come across a comprehensive overview of the different use cases of the Now() function in PostgreSQL.

Table of Contents

• What is PostgreSQL Now() Function?
• Advantages of using PostgreSQL Now() Function
• PostgreSQL Now Function: Numerous Use Cases
  • PostgreSQL Now(): Set Different Time Zone
  • PostgreSQL Now(): Not display Timestamp
  • PostgreSQL Now(): View any Other Day
  • PostgreSQL Now(): Add/subtract Days
  • PostgreSQL Now(): Display only Date
  • PostgreSQL Now(): Display only Time
  • PostgreSQL Now(): Display without milliseconds
• Conclusion

What is PostgreSQL Now Function?

The Now() function is used to return the current date and time of the time zone (default or user-defined). Its return type is the timestamp with the time zone. It doesn’t need any parameters.

1) Syntax

The syntax for the Now() function in PostgreSQL is:

2) Parameters or Arguments

The Now() function doesn’t require any parameters or arguments.

3) Example

SELECT NOW();

3) Output

Advantages of using PostgreSQL Now() Function

Some of the advantages of using the Now() Function in PostgreSQL are as follows:

• You use the Now() function provided by PostgreSQL to get the current date and time according to UTC.
• You can use this function to display the date and/or time according to your time zone whenever there is a requirement.
• 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.

PostgreSQL Now() Function: Numerous Use Cases

• PostgreSQL Now(): Set Different Time Zone
• PostgreSQL Now(): Not display Timestamp
• PostgreSQL Now(): View any Other Day
• PostgreSQL Now(): Add/subtract Days
• PostgreSQL Now(): Display only Date
• PostgreSQL Now(): Display only Time
• PostgreSQL Now(): Display without milliseconds

1) PostgreSQL Now(): Set Different Time Zone

You can also change the time zone settings of the server. And then display the current date and time of that particular time zone.

For example:

SET TIMEZONE = 'America/New_York';
SELECT NOW();

Output:

2) PostgreSQL Now(): Not display Timestamp

You can use the PostgreSQL Now() function to display the current date and time of the timezone (default or user-defined) without any timestamp. You can use the “timestamp” keyword along with the Now() function.

You can do that as per the following example query.

SELECT NOW()::timestamp;

Output:

3) Postgresql Now(): View any Other Day

While displaying the current date and time of the timezone (default or user-defined), you can also add or subtract the number of days, months, and years to the current date. For this purpose, the “interval” keyword is used. “+/-” operator to determine if you want to see the date and time of the past or future.

For example,

1) For displaying the date and time of the past:

SELECT
 now(),
 now() - INTERVAL '4 years 2 hours 30 minutes'
 AS "2 hours 30 minutes 4 years back";

Output:

2) For displaying the date and time of the future:

SELECT
 now(),
 now() + INTERVAL '4 years 2 hours 30 minutes'
 AS "2 hours 30 minutes 4 years forward";

Output:

4) Postgresql Now(): Add/subtract Days

While displaying the current date and time of the timezone (default or user-defined), you can also add or subtract the number of days to the current date.

For example,

1) For displaying the date and time of the following day:

SELECT NOW() - INTERVAL '-1 DAY' AS Tomorrow;

Output:

2) For displaying the date and time of the previous day:

SELECT NOW() - INTERVAL '+1 DAY' AS Yesterday;

Output:

5) PostgreSQL Now(): Display only Date

If you want to only display the current date according to the timezone, then you can use the following query along with the Now() function. As you can see, the “cast” function is used along with the Now() function.

select cast(now() as date);

Output:

You can view the output of the above query in the right-side window.

6) PostgreSQL Now(): Display only Time

If you want to only display the current time according to the timezone then you can use the following query along with the Now() function. As you can see, the “cast” function is used along with the Now() function.

select cast(now() as time);

Output:

You can view the output of the above query in the right-side window.

7) PostgreSQL Now(): Display without milliseconds

You can use the Now() function in PostgreSQL to display the current date and time without any mention of milliseconds. There are even several ways of doing this apart from using the PostgreSQL Now() function.

For example,

SELECT now()::timestamp(0);

Several different methods:

---1
SELECT date_trunc('second', now()::timestamp);
---2
SELECT DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp);
---3
SELECT DATE_TRUNC('minute', CURRENT_TIMESTAMP::timestamp);

Output:

You can see that the output for all four queries is the same on the right side output window.

For any further information on the PostgreSQL Now() function, you can visit here.

Conclusion

This article illustrated the usage of the Now() Function in PostgreSQL. You had an in-depth understanding of different ways you can use and implement the PostgreSQL Now() function.

Now, you can move forward and use the Now() function to view the current date and time.

Want to explore more about the functions related to date and time in PostgreSQL? You can go through these articles.

• PostgreSQL Convert DateTime to Date: 10 Practical Examples Simplified
• 4 Easy Ways to Convert Timestamp to Date PostgreSQL

With PostgreSQL as your data source, ingesting and moving data into your selected warehouse utilizing ETL for Data Analysis may be a time-consuming task. When you consider how much money and resources are required to engage data engineers and analysts to make sense of this data, the situation becomes even more daunting.

However, with Hevo at your fingertips, you won’t have to worry about your PostgreSQL Data Replication demands. Loading your data into your selected data warehouse will only take a few minutes.

Hevo’s strong integration with 150+ Data Sources (including 40+ Free Sources) like PostgreSQL, you can export data from your preferred data sources and load it to your desired destination. It also helps you restructure and enhance your data so that it is ready for analysis. Now, you can readily save your time and focus on gaining insights and doing in-depth research on your data using BI solutions.

You can now replicate your PostgreSQL data to any data warehouse of your choice, including Amazon Redshift, Snowflake, Google BigQuery, and Firebolt.

Why don’t you give Hevo a try? Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about PostgreSQL Now() Function in the comments section below. If you have any questions, do let us know. We’d be happy to help.

Manisha Jena Former Research Analyst, Hevo Data

Manisha is a data analyst with experience in diverse data tools like Snowflake, Google BigQuery, SQL, and Looker. She has written more than 100 articles on diverse topics related to data industry.