Formatting Dates

Raw date and timestamp values from PostgreSQL come in the standard ISO 8601 format (2025-03-15 or 2025-03-15 14:30:00+00). But users, reports, and external systems often need dates in different formats: "March 15, 2025", "15/03/2025", "3:30 PM", and so on. PostgreSQL provides powerful formatting functions to convert between dates and strings in any format you need.

TO_CHAR: Dates to Strings

TO_CHAR converts a date, timestamp, or number into a formatted string.

TO_CHAR(value, format_pattern)

Basic Date Formatting

SELECT TO_CHAR(DATE '2025-08-15', 'YYYY-MM-DD');
-- Returns: '2025-08-15'

SELECT TO_CHAR(DATE '2025-08-15', 'DD/MM/YYYY');
-- Returns: '15/08/2025'

SELECT TO_CHAR(DATE '2025-08-15', 'Month DD, YYYY');
-- Returns: 'August    15, 2025'

Note that Month pads the name with spaces to a fixed width of 9 characters. To remove the padding, use the FM (fill mode) prefix:

SELECT TO_CHAR(DATE '2025-08-15', 'FMMonth DD, YYYY');
-- Returns: 'August 15, 2025'

Common Format Patterns

Here are the most frequently used format patterns:

Time Formatting

SELECT TO_CHAR(TIMESTAMP '2025-08-15 14:30:45', 'HH24:MI:SS');
-- Returns: '14:30:45'

SELECT TO_CHAR(TIMESTAMP '2025-08-15 14:30:45', 'HH12:MI:SS AM');
-- Returns: '02:30:45 PM'

SELECT TO_CHAR(TIMESTAMP '2025-08-15 09:05:03', 'HH12:MI AM');
-- Returns: '09:05 AM'

The FM Prefix (Fill Mode)

By default, TO_CHAR pads numbers with leading zeros and month/day names with trailing spaces. The FM prefix suppresses this padding:

-- With padding (default)
SELECT TO_CHAR(DATE '2025-03-05', 'Month DD');
-- Returns: 'March     05'

-- Without padding (FM)
SELECT TO_CHAR(DATE '2025-03-05', 'FMMonth FMDD');
-- Returns: 'March 5'

You can apply FM to individual components or place it once at the start to affect everything that follows:

SELECT TO_CHAR(DATE '2025-03-05', 'FMMonth FMDDth, YYYY');
-- Returns: 'March 5th, 2025'

Ordinal Suffixes

Add TH to get ordinal suffixes (1st, 2nd, 3rd, etc.):

SELECT TO_CHAR(DATE '2025-03-01', 'FMDDth FMMonth YYYY');
-- Returns: '1st March 2025'

SELECT TO_CHAR(DATE '2025-03-22', 'FMMonth FMDDth, YYYY');
-- Returns: 'March 22nd, 2025'

Practical Examples with Table Data

Formatting order dates for a report:

SELECT
  customer_name,
  TO_CHAR(order_date, 'FMMonth FMDDth, YYYY') AS formatted_date,
  total_price
FROM orders
ORDER BY order_date;

Displaying employee hire dates with day names:

SELECT
  name,
  TO_CHAR(hire_date, 'Day, FMMonth FMDD, YYYY') AS hire_day
FROM employees;

Creating a log timestamp summary:

SELECT
  TO_CHAR(created_at, 'YYYY-MM-DD') AS log_date,
  TO_CHAR(created_at, 'HH24:MI:SS') AS log_time,
  event_type,
  severity
FROM logs
ORDER BY created_at;

Grouping by formatted month:

SELECT
  TO_CHAR(order_date, 'YYYY-MM') AS year_month,
  COUNT(*) AS total_orders,
  SUM(total_price) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY year_month;

TO_DATE: Strings to Dates

TO_DATE parses a text string into a DATE value using a format pattern.

TO_DATE(text, format_pattern)

SELECT TO_DATE('15-03-2025', 'DD-MM-YYYY');
-- Returns: 2025-03-15

SELECT TO_DATE('March 15, 2025', 'Month DD, YYYY');
-- Returns: 2025-03-15

SELECT TO_DATE('15/Aug/2025', 'DD/Mon/YYYY');
-- Returns: 2025-08-15

SELECT TO_DATE('20250315', 'YYYYMMDD');
-- Returns: 2025-03-15

This is essential when importing data from external sources that use non-standard date formats:

-- Imagine you receive dates as 'MM-DD-YYYY' strings from a US system
SELECT TO_DATE('08-15-2025', 'MM-DD-YYYY');
-- Returns: 2025-08-15

TO_TIMESTAMP: Strings to Timestamps

TO_TIMESTAMP is like TO_DATE but returns a full timestamp (with time component).

TO_TIMESTAMP(text, format_pattern)

SELECT TO_TIMESTAMP('2025-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
-- Returns: 2025-03-15 14:30:00+00

SELECT TO_TIMESTAMP('15/Mar/2025 02:30 PM', 'DD/Mon/YYYY HH12:MI PM');
-- Returns: 2025-03-15 14:30:00+00

You can also convert a Unix epoch (number of seconds) to a timestamp:

SELECT TO_TIMESTAMP(1735689600);
-- Returns: 2025-01-01 00:00:00+00

Combining Formatting with Other Functions

You can use TO_CHAR alongside other date functions for rich reporting:

SELECT
  name,
  hire_date,
  TO_CHAR(hire_date, 'FMDay') AS hired_on_day,
  TO_CHAR(hire_date, 'FMMonth YYYY') AS hired_month_year,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed
FROM employees
ORDER BY hire_date;

Building a human-readable log report:

SELECT
  TO_CHAR(created_at, 'Dy FMMonth FMDD, YYYY "at" HH12:MI AM') AS event_time,
  event_type,
  severity,
  message
FROM logs
ORDER BY created_at DESC;

Creating date ranges in a readable format:

SELECT
  customer_name,
  TO_CHAR(order_date, 'FMMonth FMDD') || ' - ' ||
    TO_CHAR(order_date + INTERVAL '7 days', 'FMMonth FMDD, YYYY') AS delivery_window
FROM orders;

Formatting Numbers with TO_CHAR

TO_CHAR can also format numeric values. While this lesson focuses on dates, it is worth knowing:

SELECT TO_CHAR(1234567.89, '9,999,999.99');
-- Returns: ' 1,234,567.89'

SELECT TO_CHAR(42, '00000');
-- Returns: ' 00042'

SELECT TO_CHAR(0.75, '99.99%');
-- Returns: '  .75%'

Formatting order totals:

SELECT
  customer_name,
  TO_CHAR(total_price, '$999,999.99') AS formatted_total
FROM orders;

Try It Yourself

1. Write a query that displays each employee's hire date in the format "Friday, March 15th, 2025" (with day name, month name, day with ordinal suffix, and year).

2. Format each order's order_date as "YYYY/MM/DD" and the total_price as a currency string with a dollar sign. Display alongside customer_name.

3. Convert the string '28-Feb-2025' into a proper DATE value using TO_DATE. Then use TO_CHAR to display it as 'February 28, 2025'.

4. Write a query that shows each log entry's created_at formatted as "HH:MI AM on Mon DD" (e.g., "02:30 PM on Apr 01"). Include the event_type and severity columns.

5. Group orders by month and display the month as a full month name (e.g., "January", "February"). Show the count and total revenue for each month.