PostgreSQL: Working with DATE Data Type and Functions

Learn how to work with the DATE data type in PostgreSQL, including formatting date output using the TO_CHAR() function.

Working with DATE Data Types and Functions in PostgreSQL

Understanding PostgreSQL's `DATE` Data Type

PostgreSQL's `DATE` data type stores calendar dates (year, month, day). The standard format for `DATE` values is YYYY-MM-DD. `DATE` values don't include time information; they only store the date. The `DATE` type uses 4 bytes of storage. The valid range is typically from 4713 BC to 5874897 AD (or 4713 BC to 5874897 AD, depending on the system's configuration). You can specify the `CURRENT_DATE` as a default value when creating a table.

Creating a Table with a `DATE` Column

This example creates a table named `Records` with a `DATE` column named `Submission_date` that uses the current date as a default value. You would use standard SQL commands to create and populate this table in PostgreSQL. Note that the `CURRENT_DATE` function gets the current date based on the database server's timezone and settings.

CREATE TABLE Records (
    Records_id SERIAL PRIMARY KEY,
    Heading VARCHAR(255) NOT NULL,
    Submission_date DATE NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO Records (Heading) VALUES
('Invoice 1'),
('Invoice 2'),
('Invoice 3');

PostgreSQL Date Functions

PostgreSQL offers various functions for working with dates. This section illustrates several of these functions. You would need to have a table with a `DATE` column to use these functions.

1. `AGE()` Function

Calculates the age (difference between two dates). You can specify a date or omit the second date to calculate the age from a given date to the current date.

SELECT AGE(Date_of_birth)
FROM Student_details;

2. `EXTRACT()` Function

Extracts specific parts of a date (year, month, day, etc.).

SELECT 
    EXTRACT(YEAR FROM Date_of_birth) AS year,
    EXTRACT(MONTH FROM Date_of_birth) AS month,
    EXTRACT(DAY FROM Date_of_birth) AS day
FROM 
    Student_details;

3. `NOW()` Function

Gets the current date and time. You can cast it to `DATE` to get only the date part.

SELECT NOW()::date;

4. Date Subtraction

The minus (-) operator calculates the difference between two dates.

SELECT NOW() - Admission_date AS Days_Since_Admission
FROM Student_details;

5. `TO_CHAR()` Function

Formats a date value into a specific string representation. It takes the date value and a format mask as parameters.

SELECT TO_CHAR(NOW()::DATE, 'YYYY/MM/DD');

Formatting Date Output in PostgreSQL with `TO_CHAR()`

Understanding `TO_CHAR()`

PostgreSQL's `TO_CHAR()` function allows you to convert a `DATE` or `TIMESTAMP` value into a custom text string representation. This is useful for formatting dates according to specific requirements and improving the readability of date-related data in your application. You provide a date or timestamp value, and a format mask specifying the desired output format.

`TO_CHAR()` Syntax

The syntax is:

TO_CHAR(date_value, format_mask);

Where:

• date_value: The date or timestamp value to format.
• format_mask: A string defining the output format (see below for common format specifiers).

Example: Formatting the Current Date

This example uses `TO_CHAR()` to format the current date using different format masks. The `NOW()` function gets the current timestamp; casting it to `DATE` (using `::DATE`) removes the time portion.

Example 1: YYYY/MM/DD Format

SELECT TO_CHAR(NOW()::DATE, 'YYYY/MM/DD');

Example 2: MON DD, YYYY Format

SELECT TO_CHAR(NOW()::DATE, 'MON DD, YYYY');

The output will be the current date formatted according to the provided mask.

Conclusion

The `TO_CHAR()` function in PostgreSQL offers a simple way to customize how date and timestamp values are displayed. This is very useful for improving the presentation of data, creating reports, or conforming to specific date formatting requirements.