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:
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.
Table of Contents
- PostgreSQL CURRENT_TIMESTAMP vs LOCALTIMESTAMP
- PostgreSQL CURRENT_TIMESTAMP Function: Basics
- PostgreSQL CURRENT_TIMESTAMP Function: Workflow Examples
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:
Output: current_timestamp ------------------------------- 2022-07-22 09:13:07.226711+00
Here’s what a basic LOCALTIMESTAMP query looks like:
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.
Scale your data integration effortlessly with Hevo’s Fault-Tolerant No Code Data Pipeline
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.
1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules.
All of this combined with transparent pricing and 24×7 support, makes us the most loved data pipeline software on review sites.
Take our 14-day free trial to experience a better way to manage data pipelines.Get Started for Free With Hevo!
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)
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
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.
SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT transaction_timestamp();
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)
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;
note_id | message | created_at ---------+------------------------------------+------------------------------ 1 | Testing current_timestamp function | 2022-07-22 10:23:42.40501+00
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:
- The Ultimate Guide to PostgreSQL Subquery for 2022
- PostgreSQL MAX() Function: Syntax, Working & Examples
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.