How to PostgreSQL - Check Date and Time Relative to Today

1. PostgreSQL - Check Date and Time Relative to Today
2. PostgreSQL NOW() Function

This tutorial demonstrates how to check the date and time relative to today in PostgreSQL.

PostgreSQL - Check Date and Time Relative to Today

While using PostgreSQL, users often face the need to use a date relative to today in their queries. PostgreSQL provides efficient functions and methods to get the current time and date.

When checking data and time relative to today, following the proper steps can help change the current region to get a different timestamp. Some functions get the task done, but it’s essential to understand the functionality to use them accurately.

Let’s start with the NOW() function below.

PostgreSQL NOW() Function

When you need to use the current date and time in your query, the NOW() function works to give you exactly that. As an output, NOW()’s return type is a timestamp with the timezone demonstrated in the following:

SELECT NOW();

Output:

2022-09-14 13:21:36.175627+07

The NOW() function must return the current date and time based on the timezone set on the database’s server. For example, if we set the timezone to Los Angeles, the function returns the current time and date of Los Angeles.

It is displayed in the following example:

SET TIMEZONE='America/Los_angeles';
SELECT NOW();

Output:

2022-09-14 02:16:21.759315-07

In the example above, the return value of NOW() is today’s current date and time adjusted to the new timezone assigned. It is also possible to get the current date and time of PostgreSQL today without a timezone if you cast it explicitly as follows:

SELECT NOW()::timestamp;

Output:

2022-09-14 02:16:21.324535

The NOW() function is quite flexible; it allows you to obtain data and time from the future. See the following example:

SELECT (NOW() + interval '1 hour') AS after_one_hour;

Output:

2022-09-14 03:16:21.759315-07

This functionality is not limited to hours; we can extend it to days and minutes. See the following code snippets:

SELECT (NOW() + interval '1 day') AS after_one_day;

Output:

2022-09-15 02:16:21.743315-07

Following is another code block:

SELECT (NOW() - interval '4 hours 15 minutes') AS after_fourhrs_15min;

Output:

2022-09-14 10:01:21.776315-07

Use the NOW() Function as Default Values

While creating your database tables, you can use NOW() as the default value for any table column. First, create a new table with the created_at column. Set it to the default value of the NOW() function.

CREATE TABLE table_name( 
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Once you insert data in the table’s columns and display it with the SELECT command, the created_at column gives timestamps for every data entry.

Alternatives of NOW() in PostgreSQL

Other than the NOW() function, there are several other functions that you can use to get the current date and time along with a timezone. Use the CURRENT_TIME or CURRENT_TIMESTAMP function to get similar results to the NOW() function.

You can use them in the following way:

SELECT CURRENT_TIMESTAMP, CURRENT_TIME;

Output:

2022-09-14 02:16:21.759315-07 | 02:16:21.759315-07

You can also get the current timestamp without timezone with functions like LOCALTIME & LOCALTIMESTAMP.

SELECT LOCALTIMESTAMP, LOCALTIME;

Output:

2022-09-14 02:16:21.759315 | 02:16:21.759315

If there is an event where you only care for the date and not the time, substitute CURRENT_DATE for NOW(), and it works out in the following way:

SELECT * FROM tablename where dateval > NOW() -interval '1 year';

Now substitute with CURRENT_DATE:

SELECT * FROM tablename where dateval > CURRENT_DATE() -interval '1 year';

It returns only the date, not the time. NOW() and all its related functions mentioned above return the start time of the current transaction. This function’s returned values are the same as they are within the transaction.

The following example may clear up any misunderstandings:

postgres =# BEGIN;
BEGIN
postgres=# SELECT NOW();
2022-09-14 02:16:21.759315-07 //output
postgres=# SELECT pg_sleep(2);
postgres=# SELECT NOW();
2022-09-14 02:16:21.759315-07 //output
postgres=# COMMIT
COMMIT

Here we call the function throughout the transaction, and the return values do not change every time.

However, if you require the current date and time that updates throughout the transaction, TIMEOFDAY makes that possible. For example, if you perform the same code as the transaction above, the current date and time will increase after pg_sleep for 2 seconds.

So, we hope this tutorial helped you learn the multiple functions that help with the NOW() function and how to execute them.