DevEquiv

    MySQL DATE_FORMAT() to PostgreSQL TO_CHAR() in Real Queries

    DATE_FORMAT() and TO_CHAR() both work for grouping and display in PostgreSQL, but neither lets a plain index satisfy a query that filters or groups by the formatted string — the query-shape implications matter more than the syntax swap.

    Close equivalent

    MySQL feature
    DATE_FORMAT()
    PostgreSQL equivalent
    TO_CHAR()

    Main limitation: Grouping by a formatted string (`GROUP BY DATE_FORMAT(created_at, '%Y-%m')`) works in both engines, but neither can use a plain index on `created_at` to satisfy that GROUP BY — both need a functional/expression index on the formatted expression itself to avoid a full scan at scale.

    Quick mapping

    MySQL DATE_FORMAT()
    PostgreSQL TO_CHAR()

    Examples

    Filtering by formatted month in a WHERE clause (anti-pattern in both)

    MySQL 
    SELECT * FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-07';
    PostgreSQL 
    SELECT * FROM orders WHERE TO_CHAR(created_at, 'YYYY-MM') = '2026-07';
    Both work, but both also prevent the query planner from using a plain index on created_at — prefer a range condition (created_at >= 2026-07-01 AND < 2026-08-01) in either engine for a fast, sargable query.

    Grouping report rows by formatted month

    MySQL 
    SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) FROM orders GROUP BY month;
    PostgreSQL 
    SELECT TO_CHAR(created_at, 'YYYY-MM') AS month, COUNT(*) FROM orders GROUP BY month;
    Both engines allow grouping by the output alias directly; the format-string token differences themselves are handled by the dedicated date-format converter.

    Caveats

    Caveat

    Grouping by a formatted string (GROUP BY DATE_FORMAT(created_at, '%Y-%m')) works in both engines, but neither can use a plain index on created_at to satisfy that GROUP BY — both need a functional/expression index on the formatted expression itself to avoid a full scan at scale.

    Caveat

    TO_CHAR()'s return type is always text; DATE_FORMAT()'s return type is a string in MySQL's connection charset. Comparing or sorting the formatted output as if it preserved chronological order is unsafe in both engines once the format string reorders date components (e.g. day before year).

    Caveat

    For the token-by-token format-string syntax differences between DATE_FORMAT() and TO_CHAR() (which specifier maps to which), see the dedicated date-format converter linked below — it is unit-tested against every supported format token, which this crosswalk page intentionally does not duplicate.

    References

    • MySQL Reference Manual — DATE_FORMAT() (v8.4) — checked 2026-07-29
    • PostgreSQL docs — to_char (v17) — checked 2026-07-29

    Last verified 2026-07-29.