Introduction to PostgreSQL NOW() function

TheNOW() function returns the current date and time with the time zone of the database server.

Here’s the basic syntax of theNOW() function:

NOW()

TheNOW() function doesn’t require any argument. Its return type is thetimestamp with time zone. For example:

SELECT NOW();

Output:

now------------------------------- 2024-01-26 18:14:09.101641-08(1 row)

Note that theNOW() function returns the current date and time based on the database server’s time zone setting.

For example, if you change the timezone to ‘Africa/Cairo’ and get the current date and time:

SET TIMEZONE='Africa/Cairo';SELECT NOW();

Output:

now------------------------------- 2024-01-27 04:15:20.112974+02(1 row)

The output indicates that the value returned by theNOW() function is adjusted to the new timezone.

Note that to get a complete list of time zones, you can query from thepg_timezone_names:

SELECT * FROM pg_timezone_names;

Partial output:

name               | abbrev | utc_offset | is_dst----------------------------------+--------+------------+-------- Africa/Abidjan                   | GMT    | 00:00:00   | f Africa/Accra                     | GMT    | 00:00:00   | f Africa/Addis_Ababa               | EAT    | 03:00:00   | f...

If you want to get the current date and time without a timezone, you can cast it explicitly as follows:

SELECT NOW()::timestamp;

Output:

now---------------------------- 2017-03-17 18:37:29.229991(1 row)

You can use the common date and time operators for theNOW() function. For example, to get 1 hour from now:

an_hour_later------------------------------ 2024-01-27 05:16:17.15237+02(1 row)

To get this time tomorrow, you add 1 day to the current time:

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

Output:

this_time_tomorrow------------------------------- 2024-01-28 04:16:28.308575+02(1 row)

To get 2 hours 30 minutes ago, you use the minus (-) operator as follows:

SELECT now()- interval'2 hours 30 minutes' AS two_hour_30_min_go;

Output:

two_hour_30_min_go------------------------------- 2024-01-27 01:47:18.246763+02(1 row)

PostgreSQL NOW() related functions

Besides theNOW() function, you can use theCURRENT_TIME orCURRENT_TIMESTAMP to get the current date and time with the timezone:

SELECT CURRENT_TIME, CURRENT_TIMESTAMP;

Output:

current_time    |       current_timestamp--------------------+------------------------------- 04:17:46.412062+02 | 2024-01-27 04:17:46.412062+02(1 row)

To get the current date and time without a timezone, you use theLOCALTIME andLOCALTIMESTAMP functions.

SELECT LOCALTIME, LOCALTIMESTAMP;

Output:

time       |         timestamp-----------------+---------------------------- 19:13:41.423371 | 2017-03-17 19:13:41.423371(1 row)

Notice thatNOW() and its related functions return the start time of the current transaction. In other words, the return values of the function calls are the same within a transaction.

The following example illustrates the concept:

postgres=#BEGIN;BEGINpostgres=#SELECT now();              now------------------------------- 2017-03-17 19:21:43.049715-07(1 row)postgres=#SELECT pg_sleep(3); pg_sleep----------(1 row)postgres=#SELECT now();              now------------------------------- 2017-03-17 19:21:43.049715-07(1 row)postgres=#COMMIT;COMMIT

In this example, we called theNOW() function within a transaction and its return values do not change through the transaction.

Note that thepg_sleep() function pauses the current session’s process sleep for a specified of seconds.

If you want to get the current date and time that does advance during the transaction, you can use theTIMEOFDAY() function. Consider the following example:

SELECT    TIMEOFDAY(),    pg_sleep(5),    TIMEOFDAY();

Output:

timeofday              | pg_sleep |              timeofday-------------------------------------+----------+------------------------------------- Sat Jan 27 04:19:08.650831 2024 EET |          | Sat Jan 27 04:19:13.655833 2024 EET(1 row)

After pausing 5 seconds, the current date and time increased.

PostgreSQL NOW() function as default values

You can use theNOW() function as the default value for a column of a table. For example:

First,create a new table named posts with thecreated_at column that has a default value provided by theNOW() function:

CREATE TABLE posts (     idSERIAL PRIMARY KEY,     titleVARCHAR NOT NULL,     created_atTIMESTAMPTZ DEFAULT Now());

Second,insert a new row into theposts table:

INSERT INTO posts (title)VALUES     ('PostgreSQL NOW function');

Third,query data from theposts table:

SELECT * FROM posts;

Output:

id |          title          |          created_at----+-------------------------+-------------------------------  1 | PostgreSQL NOW function | 2024-01-27 04:20:11.286958+02(1 row)

Even though we did not provide the value for thecreated_at column, the statement used the value returned by theNOW() function for that column.

Summary

• Use the PostgreSQLNOW() function to get the current date and time with the timezone.

Last updated onJanuary 27, 2024

On this page

• Introduction to PostgreSQL NOW() function
• PostgreSQL NOW() related functions
• PostgreSQL NOW() function as default values
• Summary