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
Ditch the manual process of writing long commands to connect your PostgreSQL and choose Hevo’s no-code platform to streamline your data migration.
With Hevo:
- Easily migrate different data types like CSV, JSON etc.
- 150+ connectors like PostgreSQL and Google Sheets(including 60+ free sources).
- Eliminate the need of manual schema mapping with the auto-mapping feature.
Experience Hevo and see why 2000+ data professionals including customers, such as Thoughtspot, Postman, and many more, have rated us 4.3/5 on G2.
Get Started with Hevo for Free
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();
- The code starts by setting the database’s time zone to ‘America/New_York’, which adjusts the time zone for any subsequent date and time functions.
- The
SELECT NOW();
statement retrieves the current date and time based on the specified time zone.
- The result will reflect the current date and time in the Eastern Time Zone (ET), accounting for daylight saving if applicable.
- This is useful for applications that need to display or log times specific to a particular geographic location.
- Both commands are typically run in a SQL environment that supports PostgreSQL syntax.
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.
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";
- The
SELECT
statement retrieves the current date and time using the now()
function.
- It calculates a new date and time by subtracting a time interval of ‘4 years 2 hours 30 minutes’ from the current time.
- The result of this calculation is given the alias “2 hours 30 minutes 4 years back” for easier identification in the output.
- The query provides both the current date and time and the date and time exactly 4 years, 2 hours, and 30 minutes earlier.
- This is useful for comparing the current date with a specific point in the past.
Output:
Integrate Firebase Analytics to PostgreSQL
Integrate MongoDB to PostgreSQL
Integrate PostgreSQL to BigQuery
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";
- The
SELECT
statement retrieves the current date and time using the now()
function.
- It calculates a future date and time by adding an interval of ‘4 years 2 hours 30 minutes’ to the current time.
- The result of this calculation is labeled as “2 hours 30 minutes 4 years forward” for easy reference in the output.
- The query provides both the current date and time and the date and time exactly 4 years, 2 hours, and 30 minutes ahead.
- This is useful for planning or analyzing dates in the future based on the current time.
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 Data seamlessly Within Minutes!
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);
- The
SELECT
statements use the DATE_TRUNC
function to round down the current timestamp to a specified unit of time.
- In the first query,
date_trunc('second', now()::timestamp)
truncates the current timestamp to the nearest second, removing any fractional seconds.
- The second query,
DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp)
, achieves the same result as the first but uses CURRENT_TIMESTAMP
instead of now()
.
- The third query,
DATE_TRUNC('minute', CURRENT_TIMESTAMP::timestamp)
, truncates the current timestamp to the nearest minute, removing seconds.
- These functions are useful for standardizing time data, making it easier to group or compare timestamps without dealing with smaller units like milliseconds or seconds.
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.
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.
FAQ on PostgreSQL Now() Function
How to get the current timestamp in Postgres?
Get the current timestamp in Postgres using the CURRENT_TIMESTAMP or NOW() function in your SQL query.
What is the difference between the current timestamp and now in PostgreSQL?
CURRENT_TIMESTAMP and NOW() both return the current date and time. They are functionally equivalent in PostgreSQL.
What is the default CURRENT_TIMESTAMP in PostgreSQL?
The default CURRENT_TIMESTAMP in PostgreSQL returns the date and time at the start of the current transaction.
How to query PostgreSQL timestamp?
Query PostgreSQL timestamp by selecting the timestamp column in an SQL query and using date/time functions for filtering or formatting as needed.
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 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.