PostgreSQL provides multiple built-in functions to deal with the date and time, such as TO_DATE(), LOCALTIMESTAMP, NOW(), CURRENT_TIMESTAMP, and so on. All these functions perform different functionalities on the date and time values. For instance, the TO_DATE() function is used for string-to-date conversion, the LOCALTIMESTAMP function retrieves the current date and time, etc.

This write-up will present a comparative analysis of the Postgres CURRENT_TIMESTAMP and LOCALTIMESTAMP functions with examples. So, let’s get started.

What is Difference Between the CURRENT_TIMESTAMP and LOCALTIMESTAMP Functions?

In Postgres, the current/latest date and time at which the transaction started can be obtained using the CURRENT_TIMESTAMP and LOCALTIMESTAMP functions. So, what's the distinction between these two functions? Well! It’s the time zone that makes a key difference. The CURRENT_TIMESTAMP retrieves the date and time along with the time zone, while the LOCALTIMESTAMP function retrieves the date and time without the time zone.

Example #1: CURRENT_TIMESTAMP VS LOCALTIMESTAMP in PostgreSQL

Let us implement these functions one by one and see where they differ:

SELECT CURRENT_TIMESTAMP;

The resultant output proved that the CURRENT_TIMESTAMP function retrieves the timestamp and the time zone.

Now, it’s time to implement the LOCALTIMESTAMP function practically:

SELECT LOCALTIMESTAMP;

From the resultant output, it is clear that the LOCALTIMESTAMP function returns a timestamp without a time zone.

Example #2: CURRENT_TIMESTAMP VS LOCALTIMESTAMP With Precision Parameter

In Postgres, both CURRENT_TIMESTAMP and LOCALTIMESTAMP functions can accept a precision parameter that allows us to specify the fractional seconds up to the specific number of digits:

SELECT CURRENT_TIMESTAMP(3);

In this example, we specified 3 as a precision parameter, so the CURRENT_TIMESTAMP function retrieves the seconds with only three fractional points.

SELECT LOCALTIMESTAMP(3);

The output clarifies that the LOCALTIMESTAMP function keeps only three digits for the seconds and skips the rest of the fractional points.

That’s all you need to know about the Postgres CURRENT_TIMESTAMP VS LOCALTIMESTAMP functions.

Conclusion

In Postgres, the only difference between the CURRENT_TIMESTAMP and LOCALTIMESTAMP functions is the time zone. The current/latest date and time at which the transaction started can be obtained using the CURRENT_TIMESTAMP and LOCALTIMESTAMP functions. The CURRENT_TIMESTAMP retrieves the date and time along with the time zone, while the LOCALTIMESTAMP function retrieves the date and time without the time zone. A couple of examples were considered in this write-up to explain the difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP functions.