PostgreSQL – Date Data Type

PostgreSQL offers powerful DATE data type and date functions to efficiently handle date and time information. PostgreSQL DATE data type allows for storing and manipulating calendar dates while its robust set of date functions enables users to perform operations like date arithmetic and formatting.

In this article, We will learn about the Date Data Type in PostgreSQL by understanding various examples and comprehensive date and time handling.

Introduction to PostgreSQL Date Data Types

PostgreSQL offers several date and time data types to represent and manipulate calendar dates, timestamps, and time intervals.

These data types allow us to store and query both simple date values (like 2024-10-10) and more complex ones that include time zones, timestamps, and intervals.

The common PostgreSQL date data types are:

1. DATE: Stores date values without time (e.g., ‘2024-10-10’).
2. TIMESTAMP: Stores both date and time values (e.g., ‘2024-10-10 14:30:00’).
3. TIMESTAMPTZ: A TIMESTAMP with a time zone.
4. TIME: Stores only the time (e.g., ’14:30:00′).
5. INTERVAL: Represents a time span (e.g., ‘1 year 2 months’).

DATE Data Type in PostgreSQL

The DATE data type in PostgreSQL stores calendar dates without any associated time values. It follows the format YYYY-MM-DD (Year-Month-Day).

Storing and Querying Dates

When we insert a date into a PostgreSQL table, we simply need to define a column with the DATE data type. PostgreSQL will handle the formatting and ensure that it’s stored correctly.

Example 1: Inserting Dates

Let’s create a table that stores information about employees, including their birthdates.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    birthdate DATE
);

Now, insert some date values:

INSERT INTO employees (name, birthdate)
VALUES 
('Alice', '1990-01-15'),
('Bob', '1985-07-23'),
('Charlie', '2000-03-02');

Example 2: Querying Date Values

To retrieve employees born before 1990:

SELECT name, birthdate
FROM employees
WHERE birthdate < '1990-01-01';\\

Output:

TIMESTAMP and TIMESTAMPTZ Data Types

Difference Between TIMESTAMP and TIMESTAMPTZ

• TIMESTAMP: It Stores date and time without any time zone information. It uses the format YYYY-MM-DD HH:MM.
• TIMESTAMPTZ: It is Similar to TIMESTAMP, but it includes time zone information. PostgreSQL will automatically convert the time to the specified time zone.

Example 1: Storing Timestamps

Create a table for event scheduling:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(50),
    event_start TIMESTAMP
);

Insert some events:

INSERT INTO events (event_name, event_start)
VALUES 
('Conference', '2024-12-01 09:00:00'),
('Webinar', '2024-12-05 14:00:00');

Example 2: Querying Timestamps with Time Zone

SELECT event_name, event_start
FROM events;

Output:

TIME Data Type

The TIME data type stores only the time of the day, without any date.

Example 1: Inserting and Querying Time Values

CREATE TABLE office_hours (
    id SERIAL PRIMARY KEY,
    day VARCHAR(50),
    open_time TIME,
    close_time TIME
);

INSERT INTO office_hours (day, open_time, close_time)
VALUES 
('Monday', '09:00:00', '17:00:00'),
('Friday', '10:00:00', '16:00:00');

Query the office hours:

SELECT day, open_time, close_time
FROM office_hours;

Output:

INTERVAL Data Type

The INTERVAL data type represents a span of time (e.g., ‘2 years’, ‘3 days’).

Example 1: Performing Date Arithmetic

Let’s calculate the age of employees by using the INTERVAL type.

SELECT name, birthdate, AGE(birthdate) AS age
FROM employees;

Output:

Common Date Functions in PostgreSQL

1. NOW()

The NOW() function returns the current date and time.

SELECT NOW();

Output:

2. AGE()

The AGE() function calculates the difference between two dates.

SELECT AGE('2024-10-10', '1990-01-15') AS age_difference;

Output:

3. EXTRACT()

The EXTRACT() function extracts parts of a date (year, month, day).

SELECT EXTRACT(YEAR FROM birthdate) AS birth_year
FROM employees;

Output:

4. TO_CHAR()

The TO_CHAR() function is used for formatting date and time output.

SELECT TO_CHAR(NOW(), 'DD/MM/YYYY') AS formatted_date;

Output:

Important Points About the Date Data Type in PostgreSQL

• The DATE data type in PostgreSQL requires 4 bytes of storage and supports a range from 4713 BC to 5874897 AD.
• PostgreSQL uses the ‘yyyy-mm-dd' format for storing and inserting date values.
• PostgreSQL supports date arithmetic, allowing you to add or subtract intervals from dates.
• PostgreSQL offers a variety of date functions for manipulating and extracting parts of dates. Some commonly used functions include AGE(), EXTRACT(), DATE_TRUNC(), and TO_CHAR().

Conclusion

Overall, PostgreSQL provides a versatile timestamp data type and advanced time zone handling, allowing for precise date-time management across different regions. Functions like NOW return the current timestamp, while TO_CHAR formats date and time values. The AGE function calculates time differences, and EXTRACT allows you to retrieve specific parts of a date or time. These powerful features make PostgreSQL a robust solution for handling complex date-time operations in any application.

FAQs for PostgreSQL Date Data Types

What is the difference between TIMESTAMP and TIMESTAMPTZ in PostgreSQL?

TIMESTAMP stores both date and time without considering time zones, whereas **TIMESTAMPTZ** includes time zone information, automatically converting the stored timestamp to the session’s time zone.

How do I calculate the difference between two dates in PostgreSQL?

You can use the **AGE()** function in PostgreSQL to calculate the time difference between two dates. For example, `SELECT AGE(‘2024-10-10’, ‘1990-01-15’)` will return the age difference in years, months, and days.

How can I format date and time in PostgreSQL?

PostgreSQL offers the **TO_CHAR()** function to format date and time output. For example, `SELECT TO_CHAR(NOW(), ‘DD/MM/YYYY’)` will return the current date in the format ’10/10/2024′.

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL-dataTypes