# PostgreSQL TIMESTAMP Data Types and Functions

> PostgreSQL provides two temporal data types to deal with the TIMESTAMPS: TIMESTAMP(without the time zone) and TIMESTAMPTZ(with a time zone).

PostgreSQL facilitates us with two temporal data types to deal with the **TIMESTAMPS**. For instance, **TIMESTAMP** and **TIMESTAMPTZ** : the first one stores the TIMESTAMP without the timezone, while the other one stores the TIMESTAMP with a timezone. Both these data types consume 8 bytes to store a TIMESTAMP value.

TIMESTAMP data types and functions will be explained in this Postgres blog using Practical examples. So, let’s get started!

 **PostgreSQL TIMESTAMP Data Types**

In Postgres, the **TIMESTAMP** data type is used to store the date and time without any time zone information. Therefore, changing a database's timezone will not update the timestamp values already stored in the database. The below snippet demonstrates how to create a column with TIMESTAMP data type:
    
    
    CREATE TABLE tab_name (
    col_name TIMESTAMP
    );

Specify the table name in place of “tab_name”. Next, specify the column name followed by the TIMESTAMP data type.

Use the Postgres’ **TIMESTAMPTZ** data type to store the date, time, and time zone information. The below syntax shows the usage of the TIMESTAMPTZ data type:
    
    
    CREATE TABLE tab_name (
    col_name TIMESTAMPTZ
    );

 **Note:** A value inserted into a column declared with the TIMESTAMPTZ data type will be converted into a universal time-coordinated value( **UTC** ). While Postgres converts the UTC value back to the **TIMESTAMPTZ** type when someone queries that value from the database.

 **Example 1: How to Create a Table Using TIMESTAMP and TIMESTAMPTZ in Postgres?**

To create a table in Postgres with a TIMESTAMP and TIMESTAMPTZ column, you can use the CREATE TABLE statement as follows:
    
    
    CREATE TABLE timestamp_example(
    time_ts TIMESTAMP NOT NULL,
    time_tz TIMESTAMPTZ NOT NULL
    );

The output snippet proves that the “ **timestamp_example** ” table has been created successfully. Let’s learn how to insert data into timestamp columns:
    
    
    INSERT INTO timestamp_example(time_ts, time_tz)
    VALUES ('2022-12-22 01:13:41.104131', '2022-12-22 01:13:41.104131-08');

The above snippet verifies that the INSERT INTO command was executed successfully. Use the “ **SELECT *** ” command to verify the newly inserted data:
    
    
    SELECT * FROM timestamp_example;

The output shows that the **TIMESTAMP** with timezone and without timezone has been inserted into the respective columns.

 **Example 2: How to Set the Current TIMESTAMP as a Column Default Value?**

Use the DEFAULT keyword along with the CURRENT_TIMESTAMP function to set the current timestamp as the default value of a column:
    
    
    CREATE TABLE emp_example(
    emp_id SERIAL PRIMARY KEY,
    emp_name TEXT,
    emp_login TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    );

The **CURRENT_TIMESTAMP** function retrieves the time with the timezone, so by default, the **emp_login** column will accept the current date, time, and timezone:

The above snippet shows that the “emp_example” table has been created successfully. Now, execute the “ **INSERT INTO** ” command to insert the employee’s data into the “ **emp_example** ” table:
    
    
    INSERT INTO emp_example(emp_name)
    VALUES('Joseph'),
    ('Joe'),
    ('Alexa'),
    ('Natie'),
    ('Alex');

Five records have been inserted into the “ **emp_example** ” table. Let’s query the inserted data using the “ **SELECT *** ” command:
    
    
    SELECT * FROM emp_example;

The output snippet proves that the current timestamp has been successfully inserted into the “ **emp_login** ” column.

 **Postgres TIMESTAMP Functions**

PostgreSQL provides several functions that allow you to manipulate and work with timestamps. Here are some frequently used timestamp functions that you might find useful:

  * [CURRENT_TIMESTAMP](<https://www.commandprompt.com/education/postgresql-current_timestamp-function-with-examples/>): Retrieves the current date and time as a timestamp.
  * [NOW()](<https://www.commandprompt.com/education/postgresql-now-function-with-practical-examples/>): Retrieves today’s date and time as a TIMESTAMP.
  * [DATE_TRUNC(field, timestamp):](<https://commandprompt.com/education/how-to-use-date_trunc-function-in-postgresql/>) Truncates/trims a timestamp to the specified field, such as a month, year, etc.
  * [TO_TIMESTAMP(time_string, format)](<https://www.commandprompt.com/education/postgresql-to_timestamp-function-with-examples/>): Converts a string to a timestamp according to the given format.
  * [AGE(timestamp_1, timestamp_2)](<https://www.commandprompt.com/education/postgresql-age-function-with-examples/>): Calculates the difference between two timestamps and returns the result as an interval.
  * [EXTRACT(field FROM timestamp)](<https://commandprompt.com/education/how-to-use-extract-function-in-postgresql/>): Extracts a particular field, such as a month, day, year, etc., from a timestamp.



 **Note:** A wide range of TIMESTAMP functions are available in Postgres; only a few are discussed here. You can visit the following Postgres guide to learn more about the [TIMESTAMP](<https://www.commandprompt.com/education/date-time-functions-in-postgresql-with-examples/>) functions.

 **Conclusion**

PostgreSQL provides two temporal data types to deal with the TIMESTAMPS: **TIMESTAMP** and **TIMESTAMPTZ**. The first one stores the TIMESTAMP without the timezone, while the second one stores the **TIMESTAMP** with a timezone. Moreover, Postgres provides various built-in timestamp functions that allow us to manipulate and work with timestamps, such as NOW(), CURRENT_TIMESTAMP, AGE(), etc. This Postgres guide presented a thorough guide on using the TIMESTAMP data types and Functions in Postgres.

---
[View this page online](https://www.commandprompt.com/education/postgresql-timestamp-data-types-and-functions/)