PostgreSQL CURRENT_TIMESTAMP Function: Syntax, Usage & Examples

The PostgreSQL Current_Timestamp Function returns, as an output, the current time and date of the session timezone. Here, the time zone offset reflects the query session’s current local time.

In addition, the function query also takes in an optional argument, Precision. As the name would suggest, the Precision argument results in the time value output with a fractional second accuracy. By default, the output value only goes up to 6 decimal places.

An easier way to understand would be through an example. For instance, you would want the exact time a transaction would have started. The PostgreSQL Current_Timestamp Function helps you find the same with precision up to 6 decimal places.

The syntax looks like this:

CURRENT_TIMESTAMP(precision)

In this tutorial article, we will learn how to use the PostgreSQL CURRENT_TIMESTAMP Function skillfully through some workflow examples. We will also learn about the Precision argument to provide the most precise output.

Let’s begin.

Table of Contents

1. PostgreSQL CURRENT_TIMESTAMP vs LOCALTIMESTAMP
2. PostgreSQL CURRENT_TIMESTAMP Function: Basics
3. PostgreSQL CURRENT_TIMESTAMP Function: Workflow Examples
4. Conclusion

PostgreSQL CURRENT_TIMESTAMP vs LOCALTIMESTAMP

Both, CURRENT_TIMESTAMP and LOCALTIMESTAMP, return a timestamp value, with a major difference being, CURRENT_TIMESTAMP returning a value with a TIME ZONE specified.

Here’s what a basic CURRENT_TIMESTAMP query looks like:

SELECT CURRENT_TIMESTAMP;

The Output:

Output:

       current_timestamp       
-------------------------------
 2022-07-22 09:13:07.226711+00

Here’s what a basic LOCALTIMESTAMP query looks like:

SELECT LOCALTIMESTAMP;

The Output:

      localtimestamp       
---------------------------
 2022-07-22 09:13:27.14538
(1 row)

Now that the difference between the two timestamp options is clear, let’s learn the PostgreSQL CURRENT_TIMESTAMP Function in detail.

Basics of PostgreSQL CURRENT_TIMESTAMP Function

The CURRENT_TIMESTAMP Function returns the output as the current date, time, and time zone offset using the machine’s date, time, and time zone on which PostgreSQL is running.

The output is returned in this format: “YYYY-MM-DD hh:mm:ss.nnnnnn+/-tz”

The following are the specifications for the above-given format:

• YYYY: 4-digit year
• MM: 2-digit month
• DD: 2-digit day of the month
• hh: 2-digit hour
• mm: 2-digit minute
• ss: 2-digit second
• nnnnnn: the number of fractions of a second (This is the precision, from 0-6)
• +tz or -tz: the time zone offset (‘+’ or ‘-‘ from UTC)

If you would like to add precision to your query, the query and its output would look like this:

SELECT current_timestamp (4)

The Output:

      current_timestamp      
-----------------------------
 2022-07-22 09:32:52.6573+00
(1 row)

Notice how after specifying the precision value in brackets with an integer (from 0 to 6) the output obtained is with the desired number of fractional seconds.

Now, look at some real-time examples.

Workflow Examples to Better Understand PostgreSQL TIME_STAMP

#Example 1

The following example showcases the time and timestamp of all three functions. In PostgreSQL, a column’s current timestamp is used as the default value, and the current timestamp and time are used to deliver values with the time zone. The current statement’s start time is the time of the most recent command received from you.

The Code:

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT transaction_timestamp();

The Output:

  current_timestamp       
-------------------------------
 2022-07-22 09:47:57.912718+00
(1 row)

             now              
------------------------------
 2022-07-22 09:47:57.91313+00
(1 row)

     transaction_timestamp     
-------------------------------
 2022-07-22 09:47:57.913481+00
(1 row)

#Example 2

First, make a table called note with a created at column that is a TIMESTAMP WITH TIME ZONE column.

CREATE TABLE note(
    note_id serial PRIMARY KEY,
    message varchar(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

The result of the CURRENT TIMESTAMP() function provides the default value for the created at column.

Second, add a new row to the note table as follows:

INSERT INTO note(message) 
VALUES('Testing current_timestamp function');

Because we did not specify a value for the created at column in this statement, it defaulted to the timestamp at which the transaction began.

Third, use the following query to ensure that the insert was properly performed:

SELECT
    *
FROM
    note;

The output:

 note_id |              message               |          created_at          
---------+------------------------------------+------------------------------
       1 | Testing current_timestamp function | 2022-07-22 10:23:42.40501+00

Conclusion

This article provided an in-depth knowledge of the PostgreSQL CURRENT_TIMESTAMP Function. Through some workflow examples, we learned how to produce time precise transaction value. We also discussed how PostgreSQL CURRENT_TIMESTAMP is different from LOCALTIMESTAMP.

If you want to continue your learning of PostgreSQL Commands & Operations, go through either of the two articles provided below:

1. The Ultimate Guide to PostgreSQL Subquery for 2022
2. PostgreSQL MAX() Function: Syntax, Working & Examples

VISIT OUR WEBSITE TO EXPLORE HEVO

Hevo, a No-code Data Pipeline, provides you with a consistent and reliable solution for seamless data replication from a wide variety of Sources & Destinations — that, too, in just a few clicks!

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also look at the amazing price, which will assist you while selecting the best plan for your requirements.