Postgres supports a couple of built-in data types to store and manipulate the timestamps, such as the TIMESTAMP and TIMESTAMPTZ. The TIMESTAMPTZ data type stores the DateTime with timezone while the TIMESTAMP stores the DateTime without timezone information. Both these data types take eight bytes to store a DateTime value in the database.

This post presents a thorough guide on how to work with timestamp data types with or without precision.

PostgreSQL Timestamp Data Types With or Without Precision

Postgres offers numerous built-in functions to work with the TIMESTAMP data types, such as NOW(), CURRENT_TIMESTAMP, etc. These functions return the timestamp with precision. The term “precision” represents the fractional points kept in the seconds' field. To get a timestamp without precision an optional parameter “p” must be set to “0”.

Let’s comprehend the TIMESTAMP data types via the below-given examples.

Example 1: TIMESTAMP With Precision

In the following code, the sample table is created with two columns: emp_name and check_in.

CREATE TABLE emp_details (
emp_name TEXT,
joining_date_time TIMESTAMPTZ
);

Let’s use the CURRENT_TIMESTAMP function to insert the current DateTime in the joining_date column:

INSERT INTO emp_details (emp_name, joining_date_time)
VALUES ('Joe', CURRENT_TIMESTAMP);

Now, check out the table’s data using the “SELECT” command:

SELECT * FROM emp_details;

The output shows that the current DateTime is added to the selected table with precision.

Example 2: TIMESTAMP Without Precision

To get a timestamp without precision, specify “0” as an argument to the timestamp data type while table creation:

CREATE TABLE emp_details_1 (
emp_name TEXT,
joining_date_time TIMESTAMPTZ(0)
);

Now use the INSERT query to insert the current DateTime in the joining_date column:

INSERT INTO emp_details_1 (emp_name, joining_date_time)
VALUES ('Joe', CURRENT_TIMESTAMP);

To verify the table’s data, execute the “SELECT *” command:

SELECT * FROM emp_details_1;

This way, you can store a timestamp without precision.

Example 3: TIMESTAMP With or Without Precision

The below code demonstrates the comparative analysis of retrieving the timestamp with or without precision:

SELECT CURRENT_TIMESTAMP AS with_precision,
CURRENT_TIMESTAMP(0) AS without_precision;

This is how you can get the TIMESTAMP with or without precision.

Conclusion

Postgres offers a couple of built-in temporal data types, such as the TIMESTAMP and TIMESTAMPTZ. By default, these data types store the DateTime values with precision. To get a timestamp without precision, users need to specify “0” as an argument to the timestamp data type or timestamp functions. This post presented a detailed guide on timestamp data types with or without precision.