What is PostgreSQL Now() Function (Syntax+Examples)?

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.

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

1) How to Set Different Time Zone Using Now()?

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) How to Get Current Date and Timezone without Timestamp Using Now()?

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) How to View any Other Day Using Now()?

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:

Integrate Firebase Analytics to PostgreSQL

Get a DemoTry it

Integrate MongoDB to PostgreSQL

Get a DemoTry it

Integrate PostgreSQL to BigQuery

Get a DemoTry it

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) How to Add/subtract Days by Using Now()?

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) How to Display only Date Using Now() ?

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) How to Display only Time Using Now() ?

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.

Migrate Your PostgreSQL Data Seamlessly Within Minutes!

Start For Free

No credit card required

7) How to Display Current Date and Time 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 the official documentation.

Also, get a better understanding of your PostgreSQL data by exploring how you can convert DateTime to Date and also converting Timestamp to Date.

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.

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.

Try Hevo and see the magic for yourself. Sign up for a free 14-day trial to streamline your data integration process. You may examine Hevo’s pricing plans and decide on the best plan for your business needs.

FAQ on PostgreSQL Now() Function

Manisha Jena Research Analyst, Hevo Data

Manisha Jena is a data analyst with over three years of experience in the data industry and is well-versed with advanced data tools such as Snowflake, Looker Studio, and Google BigQuery. She is an alumna of NIT Rourkela and excels in extracting critical insights from complex databases and enhancing data visualization through comprehensive dashboards. Manisha has authored over a hundred articles on diverse topics related to data engineering, and loves breaking down complex topics to help data practitioners solve their doubts related to data engineering.

×

Exploring 3 Methods for Data Replication from Database

Download Now