How to Format Dates in PostgreSQL with examples?

PostgreSQL Date Formatting Explained

Formatting dates in PostgreSQL is essential for displaying date values in a user-friendly or specific format. PostgreSQL provides several functions, such as TO_CHAR, to customize how date and time data appear. This guide explores the syntax, examples, and best practices for formatting dates in PostgreSQL.

Syntax:

To format a date in PostgreSQL, the most commonly used function is TO_CHAR.

TO_CHAR(date_value, 'format')

Components:

• date_value: The input date or timestamp.
• format: The string representing the desired format.

Common Formatting Patterns

Examples and Code

1. Format Date to YYYY-MM-DD

Code:

-- Format a date to 'YYYY-MM-DD'
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS formatted_date;

Output:

formatted_date  
--------------
2024-01-15

2. Display Full Month and Year

Code:

-- Display date as 'Month YYYY'
SELECT TO_CHAR(CURRENT_DATE, 'Month YYYY') AS formatted_date;

Output:

formatted_date  
--------------
January 2024

3. Custom Format with Day, Month, and Time

Code:

-- Display date as 'Day, DD Mon YYYY HH12:MI AM'
SELECT TO_CHAR(NOW(), 'Day, DD Mon YYYY HH12:MI AM') AS formatted_date_time;

Output:

formatted_date_time  
--------------------
Monday, 15 Jan 2024 01:30 PM

4. Retrieve Month and Day Separately

Code:

-- Get the month and day separately
SELECT 
    TO_CHAR(CURRENT_DATE, 'MM') AS month,
    TO_CHAR(CURRENT_DATE, 'DD') AS day;

Output:

month | day  
------+-----
01    | 15

Explanation

1. Using TO_CHAR for Formatting:

The TO_CHAR function in PostgreSQL converts a date or timestamp into a string formatted according to the specified pattern. For instance, 'YYYY-MM-DD' converts a date into the standard ISO format.

2. Custom Formats for Readability:

PostgreSQL supports a variety of formatting codes, allowing developers to create custom date and time representations tailored to user interfaces or reports.

3. Practical Applications:

• Reports: Display dates in a user-friendly way.
• Logs: Create readable timestamps for debugging or auditing.
• APIs: Ensure consistent date formatting for frontend integration.

4. Flexibility with Localization:

PostgreSQL supports localized names for days and months if the database locale is set accordingly.

Common Errors and Tips

1. Mismatch Between Data Types:

Ensure the input to TO_CHAR is a valid date or timestamp type. Using other data types will result in errors.

2. Zero-padding in Dates:

Use FM (Fill Mode) before a pattern to remove zero-padding. Example: FMMonth instead of Month.

3. Default Date Format:

By default, PostgreSQL displays dates in the format YYYY-MM-DD. Use TO_CHAR only when customization is needed.

4. Performance Consideration:

Avoid excessive use of TO_CHAR in large queries as it can affect performance during formatting.

All PostgreSQL Questions, Answers, and Code Snippets Collection.