PostgreSQL Now() Function – A 101 Guide

|

PostgreSQL Now Function_FI

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?

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

Usage of the Now() function and its output
Image Source: Self

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.
Scale your data integration effortlessly with Hevo’s Fault-Tolerant No Code Data Pipeline

As the ability of businesses to collect data explodes, data teams have a crucial role to play in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules. 

All of this combined with transparent pricing and 24×7 support makes us the most loved data pipeline software on review sites.

Take our 14-day free trial to experience a better way to manage data pipelines.

Get started for Free with Hevo!

PostgreSQL Now() Function: Numerous Use Cases

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:

Changing Timezone to America/New York and then using PostgreSQL Now function
Image Source: Self

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:

Display date and time without timestamp by using the "timestamp" keyword along with Now() function
Image Source: Self

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:

Now function is used to display any time using Interval (past)
Image Source: Self

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:

Now function is used to display any time using Interval (future)
Image Source: Self

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:

Now function can be used to add one day with the current date and time
Image Source: Self

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

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

Output:

Now function can be used to subtract one day with the current date and time
Image Source: Self

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.

Now function can be used to display only the date along with the cast function
Image Source: Self

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.

Now function can be used to display only the time along with the cast function
Image Source: Self

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.

Now function can be used to display the current date and time without using milliseconds in the time
Image Source: Self

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.

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.

Visit our Website to Explore Hevo

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
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.

No-code Data Pipeline for PostgreSQL