PostgreSQL now function

In this PostgreSQL tutorial, we will study the use of PostgreSQL now() function that will allow us to create tasks with multiple real-time examples.

PostgreSQL now function

Before delving into the topic, let’s discuss what it is.

What is the PostgreSQL NOW() Function?

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 also modify the outcome returned by the PostgreSQL NOW() function to other time zones. Additionally, we can retrieve the current date and time without specifying a time zone. This function is generally used to assign a default value to a column in a table. Let’s check the syntax now.

How NOW() Works in PostgreSQL

The NOW() Function is an alias for the CURRENT_TIMESTAMP function in PostgreSQL. When you call NOW(), you’re essentially calling CURRENT_TIMESTAMP with identical functionality and output.

It’s important to understand that. NOW() Returns a timestamp with timezone data type, which includes:

• Date (year, month, day)
• Time (hour, minute, second, and microsecond precision)
• Time zone offset

Syntax

SELECT NOW();

Let’s check the output now.

When we use the NOW() function in PostgreSQL, we also need to verify that the current date and time value is based on the server’s default time zone settings. But, we can also modify the default settings; we can 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. After executing the above query, I got the expected output as shown in the screenshot below.

From the output, we can see that the value returned by the NOW() function is adjusted to the new time zone. Now, if we want to return the current date and time without a time zone, we can write the query as follows.

SELECT NOW()::timestamp;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Read: PostgreSQL Having Clause

Different Ways to Use NOW() in PostgreSQL

Let’s explore various use cases and implementations of the NOW() function with multiple real-time examples.

Example 1: Getting the Current Date Only

If you only need the current date without time information, we can execute the following SQL query.

SELECT NOW()::DATE;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Example 2: Getting the Current Time Only

To extract just the time component, we can use the following query.

SELECT NOW()::TIME;

After executing the above query, I got the expected output as shown in the screenshot below.

Example 3: 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. After execution, I obtained the expected output, as shown in the screenshot below.

Read: PostgreSQL while loop.

Example 4: Now UTC

PostgreSQL considers our local time zone for any type that contains only a date or time. All timezone-aware dates and times are saved internally in UTC. PostgreSQL stores timestamps in UTC values.

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

SELECT NOW() AT TIMEZONE 'UTC';

Let’s check the output now. I got the expected output after executing the above query as shown in the screenshot below.

Read: PostgreSQL Sum

Example 5: now() -interval

The interval data type in PostgreSQL is used to store and manipulate a period. It has a capacity of 16 bytes of space and spans a range of -178,000,000 years to 178,000,000 years.

It also has an additional attribute, known as “precision” (denoted by p), which 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 following examples show some interval values.

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

PostgreSQL can store interval type values in the form of days and months as integers, but for values in seconds, it can store them as fractions. Now, we will examine some examples to enhance our understanding.

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

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

After executing the above query, I got the expected output as shown in the screenshot below.

Read: PostgreSQL Loop Examples

Example 6: Now minus 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';

After executing the above query, I got the expected output as shown below.

Example 7: now() format

The now() function in PostgreSQL returns the current date and time, including the time zone. The now() function doesn’t have any parameters or arguments. The format for the 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);

After executing the above query, I got the expected output as shown in the screenshot below.

Read: PostgreSQL date to string

Example 8: now() without milliseconds

A millisecond is one-thousandth of a second. A microsecond is one-millionth of a second. There are various ways to explain the now( )function without milliseconds. Let’s review the different queries for this.

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. After executing the above query, I got the expected output as shown in the screenshot below.

Read: PostgreSQL escape single quote.

Example 9: Now() without timezone

With or without a time zone, dates and timestamps are similar. During time intervals, they are compared to another value of a comparable data type, regardless of whether the time zone is specified.

Once we differentiate a timestamp without a time zone from a timestamp with a time zone, the prior value is intended to be given within the time zone described by the TimeZone configuration parameter. It is converted to UTC for differentiation from the latest value, which is already in UTC.

Likewise, a date value is intended to represent midnight in the TimeZone when compared to a timestamp. Let’s check the syntax for now.

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

After executing the above query, I got the expected output as shown in the screenshot below.

Real-time implementation of NOW() function

One of the most common uses of NOW() it is setting default values for timestamp columns while creating the table.

We can use the query like below.

CREATE TABLE UserAccounts (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_login TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

The table was created successfully after executing the above query, as shown in the screenshot below.

Conclusion

In this tutorial, we have studied the use of PostgreSQL now, which will allow us to create functions with multiple real-time examples.

Remember these key points:

• NOW() Returns the current timestamp with timezone
• It’s an alias for CURRENT_TIMESTAMP
• It’s fixed during a transaction.
• It can be formatted, truncated, and manipulated using PostgreSQL’s rich set of date/time functions

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