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.
The syntax for the Now() function in PostgreSQL is:
2) Parameters or Arguments
The Now() function doesn’t require any parameters or arguments.
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.
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.
SET TIMEZONE = 'America/New_York';
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.
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.
1) For displaying the date and time of the past:
now() - INTERVAL '4 years 2 hours 30 minutes'
AS "2 hours 30 minutes 4 years back";
2) For displaying the date and time of the future:
now() + INTERVAL '4 years 2 hours 30 minutes'
AS "2 hours 30 minutes 4 years forward";
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.
1) For displaying the date and time of the following day:
SELECT NOW() - INTERVAL '-1 DAY' AS Tomorrow;
2) For displaying the date and time of the previous day:
SELECT NOW() - INTERVAL '+1 DAY' AS Yesterday;
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);
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);
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.
Several different methods:
SELECT date_trunc('second', now()::timestamp);
SELECT DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp);
SELECT DATE_TRUNC('minute', CURRENT_TIMESTAMP::timestamp);
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.
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.