How to Format a Date in PostgreSQL

PostgreSQL provides powerful tools to format dates into different styles and patterns using functions like TO_CHAR(). Formatting dates in PostgreSQL is important when we need to present data in a more readable or application-friendly way.

This article will explain how to format a date in PostgreSQL and cover various formatting options.

How to Format a Date in PostgreSQL?

In PostgreSQL, dates are stored in a standard format (e.g., YYYY-MM-DD). In many scenarios, we might need to display these dates in different formats such as MM-DD-YYYY, Month DD, YYYY, or even custom formats.

The most commonly used function for formatting dates in PostgreSQL is TO_CHAR(). which converts a timestamp, date, or time value to a string according to a specified format.

Formatting Dates Using TO_CHAR()

The TO_CHAR() function in PostgreSQL allows us to convert a date or timestamp to a string with a specific format.

Syntax:

TO_CHAR(date_value, 'format_pattern')

Key terms:

• date_value: The date or timestamp we want to format.
  
• format_pattern: A string that defines the format for the output.

Common Date Format Patterns

• YYYY: Four-digit year
• YY: Last two digits of the year
• MM: Month as a two-digit number (01-12)
• Mon: Abbreviated month name (e.g., Jan, Feb)
• Month: Full month name (e.g., January, February)
• DD: Day of the month (01-31)
• Day: Full day name (e.g., Monday, Tuesday)
• DY: Abbreviated day name (e.g., Mon, Tue)
• HH24: Hour (24-hour format)
• MI: Minutes
• SS: Seconds

1. How to Get a Date in the “DD/MM/YYYY” Format?

In PostgreSQL, we can format dates using the TO_CHAR() function. To display the current date in the “DD/MM/YYYY” format, we can specify the appropriate format string.

Query:

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

Output:

Explanation:

This query formats the current date (NOW()) into the DD-MM-YYYY format. For example, if today's date is September 25, 2024, the output will be 25-09-2024.

2. How to Get a Date in the “MON DD, YY” Format?

To display the current date in a more descriptive format, we can use the TO_CHAR function. By specifying the format as 'MON DD, YYYY', the date will be presented with the full month name, the day of the month, and the year, making it easy to read and understand

Query:

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

Output:

Explanation:

• The query uses the TO_CHAR function to format the current date to display the full month name, the day of the month, and the four-digit year.
  
• The resulting output provides a clear and readable date format, such as "September 25, 2024."

3. Formatting Date to MM/DD/YY

To format the current date in the MM/DD/YY format, we can use the TO_CHAR function. This format is commonly used in the United States and displays the month, day, and two-digit year.

Query:

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

Output:

Explanation:

• The query uses TO_CHAR function to format the current date into a string that follows the MM/DD/YY format.
  
• The resulting output provides a clear and concise date format, such as "09/25/24."

4. Formatting Date with Day Name and Time

To display the current date along with the day of the week and the time, we can use the TO_CHAR function with a specific format string. This format includes the full day name, the day of the month, the full month name, the year, and the time in a 24-hour format.

Query:

SELECT TO_CHAR(NOW(), 'Day, DD Month YYYY HH24:MI:SS') AS formatted_date;

Output:

Explanation:

This query includes the full day name, day of the month, full month name, year, hour, minute, and second in the formatted output.

5. Formatting Date to Display Quarter and Year

To display the current quarter of the year along with the year itself, we can use the TO_CHAR function with the appropriate format string. The Q format specifier is utilized to retrieve the quarter number.

Query:

SELECT TO_CHAR(NOW(), 'Q "Quarter of" YYYY') AS formatted_date;

Output:

Explanation:

This query uses the Q format specifier to display the quarter of the year, followed by the year. For example, 3 Quarter of 2024 if today's date falls in the third quarter of the year.

Conclusion

PostgreSQL offers various date formatting options using the TO_CHAR() function, enabling us to display dates in different ways.

This flexibility is helpful for reporting, displaying data, or adjusting date formats to meet specific business needs. By understanding these formatting patterns, we can present dates exactly as needed for our purposes.

FAQs

How do we format a date to display only the year in PostgreSQL?

We can use the TO_CHAR() function with the YYYY format pattern:

Can we format a timestamp with a time zone in PostgreSQL?

Yes, we can format a timestamp with a time zone by using the TO_CHAR() function with time-related format patterns like HH24, MI, and SS.

What is the default date format in PostgreSQL?

The default date format in PostgreSQL is YYYY-MM-DD.

A

abhay94517

Follow

Improve

Article Tags :

• PostgreSQL
• Databases