How to use PostgreSQL current_timestamp

In this PostgreSQL tutorial, I show you how to use PostgreSQL current_timestamp to get the current timestamp of the system.

Also, I will show you the way to control the format and convert the timestamp value returned by the CURRENT_TIMESTAMP to the UTC time zone.

Table of Contents

How to use PostgreSQL current_timestamp

PostgreSQL has the function current_timestamp to get time and date with timezone. The timestamp returned by this function depends upon the machine where the PostgreSQL server is running. This function is helpful when you need to know the starting time of the transaction.

The syntax is given below.

CURRENT_TIMESTAMP(precision_value)

CURRENT_TIMESTAMP: To get the current date and time with the timezone.
precision_value: It refers to the seconds part of the time to show the number of seconds of precision you want in your timestamp. By default, it shows seconds till 6 digits.

Let’s take an example and understand the PostgreSQL current_timesatmp function.

Use the below query to get the current time and date.

SELECT CURRENT_TIMESTAMP(4);

Look at the above query that shows the current date and time with the timezone as (2023-06-29 15:31:06.0273+05:30). This timestamp value is represented in the format of (YYYY-MM-DD HH: MM: SS.ssss + timezone).

How to use Postgresql current_timestamp UTC

UTC is the coordinated universal time and it is the timezone standard for worldwide to control the clocks and times. So here you will convert the timestamp returned by the CURRENT_TIMESTAMP to coordinated universal time (UTC).

Let’s see how you can get the current date and time based on the standard UTC, for that use the below command.

SELECT CURRENT_TIMESTAMP at time zone ('utc')

UTC standard Postgresql current_timestamp

The output of the query returns the date and time based on the UTC standard time. To get this type of time, the keyword ‘at time zone’ is used to specify what kind of time zone you want, then specified the time zone as ‘utc’ within parenthesis.

How to use Postgresql current_timestamp Format

The function current_timestamp() returns output in the particular format (YYYY-MM-DD HH: MM: SS.ssss + timezone). But you can control the format using the TO_CHAR() function of PostgreSQL with your desired format.

The syntax is given below.

TO_CHAR(CURRENT_TIMESTAMP, format)

The TO_CHAR() function takes two values, the timestamp, and the format to represent that timestamp. In this case, the function CURRENT_TIMESTAMP is passed as the first value to TO_CHAR() function as the timestamp value.

Let’s take an example and format the timestamp value that is returned by the function CURRENT_TIMESTAMP.

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH:MI:SS');

In the above output, the format for the date and time returned by the function CURRENT_TIMESTAMP changed to ‘YYYY/MM/DD HH:MI:SS’ using the TO_CHAR() function.

You can pass the different kinds of formats to the function TO_CHAR() to represent your timestamp value in different formats.

Conclusion

In this PostgreSQL tutorial, you learned how to get the current date and time with the timezone using the CURRENT_TIMESTAMP function. You also covered “What is UTC time zone?” and how to convert the timestamp value to UTC time zone, then learned about controlling the format of timestamp value.