Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL TO_CHAR

The PostgreSQL `TO_CHAR` function is used to convert various data types, such as dates and numbers, into formatted strings. It is useful when specific string representations of data values are required for display or further processing.

Usage

The `TO_CHAR` function is typically used when you need to format dates, times, or numbers into a specific pattern or format. It is essential in reports and user interfaces where consistent formatting is necessary.

TO_CHAR(value, format);

In this syntax, `value` is the data type you wish to format, and `format` is a string that specifies the desired output format.

Examples

1. Basic Date Formatting

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

This example formats the current date and time into a string with the format `YYYY-MM-DD`, showing only the date part. Here:

  • `YYYY` is the four-digit year.
  • `MM` is the two-digit month.
  • `DD` is the two-digit day.

2. Formatting Numbers

SELECT TO_CHAR(12345.67, 'FM99999.00');

Here, the number `12345.67` is formatted as a string with two decimal places, resulting in `12345.67`. The `FM` prefix removes leading spaces.

3. Date and Time Formatting

SELECT TO_CHAR(TIMESTAMP '2023-10-10 15:30:00', 'Day, DDth Month YYYY HH12:MI AM');

This example formats a specific timestamp into a more readable format, resulting in a string like `Tuesday, 10th October 2023 03:30 PM`. The components used here include:

  • `Day` for the full weekday name.
  • `DDth` for the day of the month with an ordinal suffix (e.g., `10th`).
  • `Month` for the full month name.
  • `HH12` for the hour in 12-hour format.
  • `MI` for minutes.
  • `AM` for the ante meridiem indicator.

4. Formatting Intervals

SELECT TO_CHAR(INTERVAL '1 year 2 months 3 days', 'YYYY "years" MM "months" DD "days"');

This example formats an interval into a descriptive string, resulting in `1 years 02 months 03 days`.

Tips and Best Practices

  • Use meaningful formats. Ensure the format string used is clear and conveys the intended meaning to users.
  • Leverage FM modifier. Use `FM` to suppress unwanted leading zeros and spaces for cleaner output.
  • Consistent formatting. Apply consistent formats across similar queries to maintain uniformity in applications and reports.
  • Validate format patterns. Test format patterns to ensure they produce the expected results across different scenarios.
  • Avoid over-formatting. Keep formatting simple to avoid confusion, especially when the string will be interpreted by users or other systems.
  • Performance considerations. Be mindful of performance when using `TO_CHAR` with large datasets, as complex formatting can impact query execution time.
  • Common pitfalls. Ensure format strings are correct to avoid unexpected results. Pay attention to case sensitivity and the correct use of format elements.

Common Error Messages

  • Invalid format specifier: Ensure that all format specifiers in the `TO_CHAR` function are correctly spelled and applicable to the data type being formatted.
  • Unsupported type for formatting: Not all data types can be directly formatted using `TO_CHAR`; ensure the data type is compatible.