Postgresql now() function

In this PostgreSQL tutorial, we will study the use of Postgresql now() that will allow us to create functions. And we are going to discuss the following list of topics.

• Postgresql now()
• Postgresql now() +30 days
• Postgresql now() -interval
• Postgresql now() utc
• Postgresql now() vs current_timestamp
• Postgresql now() – 1 day
• Postgresql now() format
• Postgresql now() without milliseconds
• Postgresql now() without timezone

Postgresql now()

The NOW() function in Postgresql is used to get the current date and time. The return type of the NOW() function is the timestamp with the time zone. We can fetch the current date and time by using the PostgreSQL NOW() function.

This function has a return type i.e. the timestamp with the time zone. Determined by the current database server’s time zone setting, the PostgreSQL NOW() function gets us the current date and time.

We can modify the outcome returned by the PostgreSQL NOW() function to other timezones too. Also, we can get the current date and time without a time zone. This function is generally used for giving the default value to the column of the table. Let’s check the syntax now.

SELECT NOW();

Let’s check the output now.

When we are using a NOW() function in PostgreSQL, we also have to verify that the current date and time value is based upon the default time zone settings in the server. But, we can also modify the default settings, we change the timezone to ‘America/New_York’. Let’s check the query for it.

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

Let’s check the output now.

From the output, we can notice that the value returned by the NOW() function is modified to the new timezone. Now, If we want to return the current date and time without a timezone, we can write the query as below.

SELECT NOW()::timestamp;

Let’s check the output for it.

Read: Postgresql Having Clause

Postgresql Now( ) +30 days

The code now() +30 DAYS in Postgresql will automatically calculate the date 30 days in the past. Let’s check the syntax for it.

SELECT NOW() -INTERVAL '+30 DAYS';
SELECT'yesterday'::TIMESTAMP,'tomorrow'::TIMESTAMP,'allballs'::TIME;

Let’s check the output for the above query.

Read: Postgresql while loop

Postgresql Now( ) UTC

PostgreSQL considers our local time zone for any type containing only date or time. All timezone-aware dates and times are saved internally in UTC. PostgreSQL stores the timestamp in UTC value.

When we insert a value into a timestamp to the column, PostgreSQL converts the timestamp value into a UTC value and stores the UTC value in the table. Let’s check the code for it.

SELECT NOW() AT TIMEZONE 'UTC';

Let’s check the output now.

Read: Postgresql Sum

Postgresql now() -interval

The interval data type in Postgresql is used to store and manipulate a time period. It has a capacity of 16 bytes of space and ranges from -178, 000, 000 years to 178, 000, 000 years.

It also has an additional attribute known as “precision (denoted by p)” that can be used to set the level of precision in the query outcome. The syntax is below.

interval [ Data_fields ] [ (p) ]

Where,
Data_fields: Time period
p: precision

The below examples show some interval values.

interval '4 months ago';
interval '5 hours 40 minutes';

PostgreSQL can store the interval type value within the integer form of days and months but for values in seconds, it can be fractions. Now we will check some examples for our better understanding.

Let’s check an example, where the query is used to know the time of 3 hours 30 minutes ago at the current time of last year using the below commands. Let’s check the query first and then output.

SELECT
 now(),
 now() - INTERVAL '2 years 3 hours 30 minutes'
 AS "3 hours 30 minutes ago of last year";

Let’s check the output now.

Read: PostgreSQL Loop Examples

Converting PostgreSQL interval to string

Let’s take another example in which we will convert an interval value to a string format using the TO_CHAR( ) function. This TO_CHAR( ) function will take the primary variable as an interval value, the second one as the format of the value, and will return a string that illustrates the interval in the represented format.

For converting an interval value to a string, we will use the TO_CHAR() function. Let’s check the format.

TO_CHAR(interval,format)

Let’s check the query now.

SELECT
    TO_CHAR(
        INTERVAL '12h 35m 24s',
        'HH24:MI:SS'
    );

Let’s check the output now.

Read: Postgresql if-else – How to use

PostgreSQL interval related operators and functions

PostgreSQL also has interval-related operators and functions in which we can apply the arithmetic operator ( +, -, *, etc.,) to the interval values. Let’s check the examples now.

SELECT
INTERVAL '3h 40m' + INTERVAL '20m'; 

SELECT
INTERVAL '3h 40m' - INTERVAL '40m'; 

SELECT
600 * INTERVAL '2 minutes'; 

Let’s check the output now.

Read: Postgres date range

Extracting data from a PostgreSQL interval

Now we will learn how to extract data from a PostgreSQL interval. To extract fields like the year, month, date, etc., from an interval, the EXTRACT() function is used. The below syntax is used for extract() function.

EXTRACT(field FROM interval)

The fields are year, month, date, hour, minutes, etc., that we can extract from the interval. The extract function will return a value of type double precision. Let’s check the below example. In this example, we will extract the minute from the interval of 7 hours 33 minutes and it will return 33 as expected.

SELECT
    EXTRACT (
        MINUTE
        FROM
            INTERVAL '7 hours 33 minutes'
    );

Let’s check the output for the above query.

Adjusting interval values

Now we will learn to adjust interval values in PostgreSQL which provides two functions justifydays and justify hours that will allow us to adjust the interval of 30-day as one month and the interval of 24-hour as one day.

In addition, the justify_interval function will adjust intervals using justifydays and justifyhours with additional sign adjustments. Let’s check the below query.

 SELECT
    justify_days(INTERVAL '20 days'),
    justify_hours(INTERVAL '14 hours');
 
SELECT
     justify_interval(interval '2 years -2 hours');
     

Let’s check the output now.

PostgreSQL interval output format

The output kind of interval values is set by using the SET intervalstyle command. Like PostgreSQL provides four output formats: sql standard, postgres, postgresverbose, and iso_8601. PostgreSQL uses the postgres style by default for formatting the interval values.

SET intervalstyle = 'sql_standard';

The following represents the interval of 7 years 6 months 4 days 3 hours 3 minutes 1 second in the four styles.

SET intervalstyle = 'sql_standard';
SELECT
	INTERVAL '7 years 6 months 4 days 3 hours 3 minutes 1 second';

SET intervalstyle = 'postgres';
SELECT
	INTERVAL '7 years 6 months 4 days 3 hours 3 minutes 1 second';

SET intervalstyle = 'postgres_verbose';
SELECT
	INTERVAL '7 years 6 months 4 days 3 hours 3 minutes 1 second';

SET intervalstyle = 'iso_8601';
SELECT
	INTERVAL '7 years 6 months 4 days 3 hours 3 minutes 1 second';

Let’s check the output now.

Read: Drop Database PSQL

Postgresql now() vs current_timestamp

The Postgresql CURRENT_TIMESTAMP() function returns the current date and time, as well as the time zone, which is the time when the transaction begins. The NOW() function in Postgresql also returns the current date and time. This return type of the NOW() function is the timestamp with the time zone.

So, there is no such difference between now() and current_timestamp. These Postgresql standard functions will return values based on the start time of the current transaction i.e. CURRENT_TIMESTAMP. Let’s check the syntax for it.

SELECT NOW(), current_timestamp;

Let’s check the output now.

Read: PostgreSQL Update + Examples

Postgresql now() – 1 day

We can cast a TIMESTAMP to a DATE, which allows us to subtract an INTEGER from it. Let’s check the syntax for -1 day.

SELECT NOW() - INTERNAL '-1 DAY';

Let’s check the output now.

Read: Postgresql date comparison

Postgresql now() format

The now() function in Postgresql will return the current date and time with the time zone. The now() function doesn’t have any parameters or arguments. The format for now function will return the current date as a ‘YYYY-MM-DD’. We use the following syntax for the now function in PostgreSQL.

select cast(now() as date);

Let’s implement this format now.

Read: Postgresql date to string

Postgresql now() without milliseconds

A millisecond is basically one-thousandth of a second. A microsecond is one-millionth of a second. There are various ways for explaining the now( )function without milliseconds. Let’s check the different queries for the same.

SELECT now()::timestamp(0);

SELECT date_trunc('second', now()::timestamp);

SELECT DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp);

SELECT DATE_TRUNC('minute', CURRENT_TIMESTAMP::timestamp);

Let’s check the output for the above queries.

Read: Postgresql escape single quote

Postgresql now() without timezone

With or without time zone, dates and timestamps are all similar, during times intervals and with or without time zone are compared to another value of a similar data type.

Once we differentiate a timestamp without a time zone to timestamp with a time zone, the prior value is meant to be given within the time zone described by the TimeZone configuration parameter and is rotated to UTC for differentiation to the latest value which is already within UTC.

Likewise, a date value is supposed to explain midnight in the TimeZone when we compare it to a timestamp. Let’s check the syntax for now.

SELECT CURRENT_TIMESTAMP(0)::TIMESTAMP WITHOUT TIME ZONE;

Let’s check the output now.

You may also like to read the following articles on PostgreSQL.

• Postgresql function return table
• PostgreSQL Date Difference Examples
• PostgreSQL list databases
• PostgreSQL Data Types + Examples

In this tutorial, we have studied the use of Postgresql now() that will allow us to create functions. And we have discussed the following list of topics.

• Postgresql now()
• Postgresql now() +30 days
• Postgresql now() -interval
• Postgresql now() utc
• Postgresql now() vs current_timestamp
• Postgresql now() – 1 day
• Postgresql now() format
• Postgresql now() without milliseconds
• Postgresql now() without timezone