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:
Common Formatting Patterns
Pattern Description Example YYYY Year (4 digits) 2024 YY Year (2 digits) 24 MM Month (2 digits) 01 Mon Abbreviated month name Jan Month Full month name January DD Day of the month 15 Day Full day name Monday DY Abbreviated day name Mon HH24 Hour (24-hour format) 13 HH12 Hour (12-hour format) 01 MI Minutes 45 SS Seconds 30Examples 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:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4