Sling Academy
Home/PostgreSQL/How to format dates in PostgreSQL

How to format dates in PostgreSQL

Last updated: January 05, 2024

Introduction

Managing and formatting dates is a common requirement in database operations. PostgreSQL offers robust functionality for working with dates and times, providing flexibility to display them in various formats according to your needs.

Understanding DATE_FORMAT Function

The DATE_FORMAT function is often the foundation for formatting dates in SQL. Although native to MySQL, PostgreSQL users will often achieve similar results using the to_char function. Before delving into complex formatting and operations, let’s ensure we understand how this function works with basic examples:

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
-- Output: 2023-03-15 16:45:00

Basic Date Formatting

Starting with the essentials, here’s how to display only the year, month, or day:


SELECT TO_CHAR(current_date, 'YYYY') AS "Year";
SELECT TO_CHAR(current_date, 'MM') AS "Month";
SELECT TO_CHAR(current_date, 'DD') AS "Day";

It’s also common to format dates to display names of days or months.


SELECT TO_CHAR(current_date, 'Day') AS "Day Name";
SELECT TO_CHAR(current_date, 'Month') AS "Month Name";

Using Patterns For Formatting

The to_char function supports a wide array of patterns to display dates in different formats:


SELECT TO_CHAR(current_date, 'FMDay, FMDDth FM of Month, YYYY') AS "Verbose Date";

Addition of FM (Fill Mode) before a pattern will remove any leading zeros or spaces for single-digit days or months.

Time Zone Conversions

PostgreSQL also allows you to convert dates to various time zones using the AT TIME ZONE clause in conjunction with to_char:

SELECT TO_CHAR(current_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') AS "UTC Time";

Interval Formatting

When working with intervals (durations between two points in time), you can format the result for clarity:

SELECT TO_CHAR(interval '5 days 3 hours', 'DD "days" HH24 "hours"') AS "Interval Format";

Advanced Date Manipulation and Formatting

Moving beyond basics, you’re able to combine date functions for more intricate tasks:


SELECT TO_CHAR(CURRENT_DATE + interval '1 month' - interval '1 day', 'FMMonth FMDDth, YYYY') AS "Last Day Next Month";

You can also extract and reformat specific elements of timestamps for comparison and filtering:

SELECT id, TO_CHAR(created_at, 'YYYY-MM-DD') AS formatted_date FROM users WHERE EXTRACT(YEAR FROM created_at) = 2022;

Localizing Dates

To localize dates according to specified locale settings, the LC_TIME setting can be utilized:

SET lc_time TO 'de_DE';
SELECT TO_CHAR(current_date, 'TMDay, TMDDth TM of TMMonth, YYYY') AS "Localized Date";

Dealing with NULL Dates

It’s important to handle NULL dates correctly in queries to avoid unexpected results. Use COALESCE or conditional logic:

SELECT TO_CHAR(COALESCE(birth_date, current_date), 'YYYY-MM-DD') FROM employees;

Conclusion

In conclusion, formatting dates in PostgreSQL requires familiarization with the to_char function and understanding the myriad of patterns it offers. With these tools, you can tailor date representations precisely to your application’s requirements.

Next Article: Explore Date Construction Functions in PostgreSQL

Previous Article: Using TO_TIMESTAMP Function in PostgreSQL: A Complete Guide

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB