What Is the Data Type for Datetime in PostgreSQL?


Heather Bennett

What Is the Data Type for Datetime in PostgreSQL?

When working with databases, it is crucial to understand how different data types are stored and manipulated. One such essential data type is datetime. In PostgreSQL, the datetime data type offers a wide range of options for storing and manipulating date and time information.

Date and Time Data Types in PostgreSQL

PostgreSQL provides several data types to handle date and time values. Let’s explore some of the most commonly used ones:

  • Date: The date data type stores only the date portion, without any time information. It follows the format YYYY-MM-DD.
  • Time: The time data type represents a specific time of day, without any date information.

    It follows the format HH:MI:SS.

  • Timestamp: The timestamp data type stores both date and time information. It follows the format YYYY-MM-DD HH:MI:SS.
  • Timestamptz: The timestamptz, or timestamp with timezone, data type is similar to timestamp but includes timezone information as well.
  • Interval: The interval data type represents a duration or a period of time, such as ‘2 days’ or ‘1 hour’.


The most commonly used datetime data type in PostgreSQL is timestamp. It allows you to store both date and time values together. Here’s an example of how you can declare a column with the timestamp data type:

CREATE TABLE my_table (
  event_timestamp TIMESTAMP

Once you have a column of type timestamp, you can insert and retrieve values using various date and time functions provided by PostgreSQL.

Working with Timestamps

PostgreSQL offers a rich set of built-in functions and operators to manipulate timestamps. Some commonly used functions include:

  • CURRENT_TIMESTAMP: Returns the current date and time.
  • EXTRACT: Extracts a specific part (year, month, day, etc.) from a timestamp.
  • DATE_PART: Similar to EXTRACT but allows specifying the part as a string.
  • TO_CHAR: Converts a timestamp to a formatted string.
  • INTERVAL: Creates an interval value from numeric values or strings.

Example Usage:

-- Get the current date and time

-- Extract the year from a timestamp
SELECT EXTRACT(YEAR FROM event_timestamp);

-- Convert timestamp to a formatted string
SELECT TO_CHAR(event_timestamp, 'YYYY-MM-DD HH24:MI:SS');

-- Add an interval of 1 day to a timestamp
SELECT event_timestamp + INTERVAL '1 day';

The above examples demonstrate just a fraction of what you can achieve with PostgreSQL’s datetime data types. Depending on your requirements, you can perform calculations, comparisons, and formatting operations with ease.

In Conclusion

Datetime handling is an essential aspect of database management. PostgreSQL provides flexible and powerful datetime data types, including timestamp, to store and manipulate date and time information. By leveraging the various built-in functions and operators, you can perform complex operations on timestamps effortlessly.

Remember to carefully consider your application’s specific requirements when choosing the appropriate datetime data type in PostgreSQL.

Discord Server - Web Server - Private Server - DNS Server - Object-Oriented Programming - Scripting - Data Types - Data Structures

Privacy Policy