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.

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.

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:

transaction_timestamp()

statement_timestamp()

clock_timestamp()

timeofday()

now()

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, and is named to exactly reflect what it returns. statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command. timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. now() is a traditional PostgreSQL equivalent to transaction_timestamp().

All the date/time data types also accept the special literal value now to specify the current date and time (again, interpreted as the transaction start time). Thus, the following three all return the same result:

SELECT CURRENT_TIMESTAMP;

SELECT now();

SELECT TIMESTAMP 'now';

How Does PostgreSQL CURRENT_TIMESTAMP() function work? 

Let’s look into the working of the current timestamp in PostgreSQL:

• PostgreSQL uses the current timestamp to retrieve the current date, time, and the time zone. It provides the information on current timestamp in PostgreSQL.
• You can use the precision parameter with the current timestamp function in PostgreSQL.
• The internally current timestamp will work as a now function in PostgreSQL. The current timestamp and now function are similar to work in PostgreSQL.
• Transaction timestamp and the current timestamp are equivalent to each other in PostgreSQL. But the transaction timestamp will reflect the same, which the function returned.

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

Example 3:

Current timestamp with precision value:

 In the following example, a precision value of 5 with the current timestamp is used. We have used five fractional seconds to define the current timestamp.

Code:

SELECT CURRENT_TIMESTAMP (5);

Output:

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. Working with PostgreSQL Subquery | A 101 Guide
2. PostgreSQL MAX() Function: Syntax, Working & Examples

VISIT OUR WEBSITE TO EXPLORE HEVO

As the ability of businesses to collect data explodes, data teams have a crucial role to play in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

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.

Tell us about your experience with PostgreSQL CURRENT_TIMESTAMP function in the comments below!

Sources: 

• Postgresql
• Educba