PostgreSQL offers multiple methods to find the current date and time with or without the time zone. In PostgreSQL, the NOW() and CURRENT_TIMESTAMP functions are used to obtain the prevailing/current time, date, and time zone. To obtain the date and time without a time zone, the LOCALTIMESTAMP and the NOW() function(with special implementation) are used.

The CURRENT_TIME function and LOCALTIME function are used to attain the current time with or without a time zone.

This write-up will present a detailed understanding of the several functions to get the current date and time in PostgreSQL.

How to Get Present/Current Date and Time in PostgreSQL Using NOW() Function?

In Postgres, the NOW() function is used to achieve the prevailing time, date, and time zone. Using the server database's time zone settings, it returns the current/up-to-date time and date. Following will be the syntax for the NOW() function:

NOW();

PostgreSQL allows us to run the NOW() function either from SQL SHELL or from the pgADMIN.

Example #1: How to Use NOW() Function in Postgres?

Let’s open the pgAdmin’s query tool and run the Postgres NOW() function to understand its working:

SELECT NOW();

The output authenticates the working of the NOW() function, as it successfully returned the current time, date, and time zone.

Example #2: How to Skip the Time Zone Using NOW() Function in PostgreSQL?

We have to cast the NOW() function explicitly to get the time and date without time zone restrictions. The below-given example will assist you in this regard:

SELECT NOW()::TIMESTAMP;

The output shows that this time the NOW() function returns a timestamp without a time zone.

How to Use CURRENT_TIMESTAMP() Function in Postgres?

In PostgreSQL, the CURRENT_TIMESTAMP function is used to obtain the current/up-to-date time, date, and time zone.

Example: How Does the CURRENT_TIMESTAMP Function Work in PostgreSQL?

Let’s consider the below snippet to get a profound understanding of the CURRENT_TIMESTAMP function:

SELECT CURRENT_TIMESTAMP;

The output shows that the CURRENT_TIMESTAMP successfully returns the current date, time, and time zone.

How to Fetch Current Time Using CURRENT_TIME Function?

In PostgreSQL, the CURRENT_TIME function is used to get the current time along with the time zone.

Example: How Does CURRENT_TIME Function Work in PostgreSQL?

The example illustrated below will explain the working of the CURRENT_TIME function:

SELECT CURRENT_TIME;

The output authenticates that the targeted function succeeded in returning the present time and time zone.

How to Obtain Current Date and Time Using LOCALTIMESTAMP Function?

This function provides us the current date and time without time zone:

Example: How to Use LOCALTIMESTAMP Function in PostgreSQL?

The below snippet will let you know the working of the LOCALTIMESTAMP function:

SELECT LOCALTIMESTAMP;

The output proves that the LOCALTIMESTAMP returns the current date and time without a time zone.

How to Get Present/Current Time Using LOCALTIME Function?

In Postgres, the LOCALTIME function doesn’t return a time zone. Instead, it returns only the current time.

Example: How to Use LOCALTIME Function in PostgreSQL?

The below-given piece of code will return the up-to-date/current time:

SELECT LOCALTIME;

Output shows that the LOCALTIME function returns the time only.

PostgreSQL CURRENT_DATE Function

In PostgreSQL, the CURRENT_DATE function can be used to retrieve the latest date.

CURRENT_DATE;

Example #1: How Does the CURRENT_DATE Function Work in Postgres?

Run the below command to retrieve the prevailing date:

SELECT CURRENT_DATE;

The output shows that the CURRENT_DATE function successfully fetched the current date.

Example #2: How to Use the CURRENT_DATE Function on Table’s Data?

Let’s say we have a table named bike_details in our database that contains the following data:

Let's suppose we want only those bikes whose launch date equals the current date. Let’s run the below-given query:

SELECT * FROM bike_details WHERE (bike_launch_date = CURRENT_DATE);

The output shows that the result set contains only those bikes whose launch date is equal to the current date. This is how you can use the CURRENT_DATE function on a table.

Conclusion

In Postgres, the NOW(), CURRENT_TIMESTAMP, and LOCALTIMESTAMP return the current time and date. The CURRENT_TIME function and LOCALTIME function are used to attain the latest time with or without a time zone. This write-up explained the usage of different date and time functions using some examples.