DevEquiv

    MySQL DATE_FORMAT() to PostgreSQL TO_CHAR() Converter

    Paste a MySQL DATE_FORMAT() specifier string to get the equivalent PostgreSQL TO_CHAR() template.

    Token-by-token mapping

    Shown one token at a time — some dialects combine two adjacent tokens (like a day number plus an ordinal suffix) into a single result above, which can differ slightly from this per-token view.

    Requires review

    MySQL DATE_FORMAT()
    %Y-%m-%d %H:%i:%s
    PostgreSQL TO_CHAR()
    YYYY-MM-DD HH24:MI:SS

    Main limitation: PostgreSQL template patterns are zero-padded by default (`MM`, `DD`); use the `FM` (fill mode) prefix — as this converter does automatically — to suppress padding where MySQL has a non-padded specifier (`%c`, `%e`).

    How it differs

    • PostgreSQL template patterns are zero-padded by default (MM, DD); use the FM (fill mode) prefix — as this converter does automatically — to suppress padding where MySQL has a non-padded specifier (%c, %e).
    • MySQL's %D (day with English ordinal suffix, e.g. "1st") is a single composite specifier; Postgres has no equivalent single token — this converter emits FMDD (the day number only) and flags the missing suffix as a warning.
    • MySQL has no timezone format specifiers at all (MySQL's DATETIME type is timezone-naive); Postgres's TZ/OF tokens therefore have no MySQL source to convert from.

    Examples

    Standard timestamp

    MySQL DATE_FORMAT() %Y-%m-%d %H:%i:%s
    PostgreSQL TO_CHAR() YYYY-MM-DD HH24:MI:SS

    A direct, exact mapping for a typical DATETIME column format.

    Ordinal day (partial conversion)

    MySQL DATE_FORMAT() %W, %D %M %Y
    PostgreSQL TO_CHAR() FMDay, FMDD FMMonth YYYY

    MySQL's `%D` composite (e.g. "1st") converts to Postgres's `FMDD` (just the number); the ordinal suffix has no Postgres template equivalent and is flagged as a warning.

    Edge cases

    Edge case

    MySQL's %D ordinal-day composite converts to Postgres's FMDD (day number only) — always review output that includes %D and add the suffix separately if your Postgres query needs it.

    Edge case

    MySQL's %v (ISO-compatible week number, used with %x) maps to Postgres's IW, but only when MySQL's week mode is set to ISO semantics (mode 3) — MySQL's plain %U/%u/%V are non-ISO and have no Postgres equivalent here.

    References

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

    Last verified 2026-07-29.