How to Format Dates in Redshift in PostgreSQL

How do I format a date to a string in Redshift?

Call TO_CHAR(date_or_timestamp, 'format_pattern'). The first argument is a DATE, TIMESTAMP, or TIMESTAMPTZ column or expression; the second is a text literal containing PostgreSQL-style format specifiers.

What are the most common format patterns?

Use YYYY (4-digit year), MM (2-digit month), DD, HH24, MI, SS, and text like "-" or ":" to build custom strings. Example: 'YYYY-MM-DD' creates ISO dates; 'Dy, Mon DD' shows abbreviated names.

How can I truncate a date to month, week, or day?

Use DATE_TRUNC('part', timestamp). Valid parts include year, quarter, month, week, day, hour, and minute. The function returns a TIMESTAMP set to the lower precision.

Which format should I store dates in?

Store dates as native DATE or TIMESTAMP columns. Format only when selecting data for reports, exporting, or interfacing with APIs.

How do I format order dates for a CSV export?

Wrap the date column with TO_CHAR: TO_CHAR(o.order_date, 'YYYY-MM-DD'). Combine with other columns to build a ready-to-load CSV.

How do I cast a string to DATE with a custom layout?

Call TO_DATE(text_value, 'format_pattern'). Example: TO_DATE('31/12/2024','DD/MM/YYYY') parses European style dates into a proper DATE.

What about timestamps and time zones?

Redshift stores TIMESTAMP as UTC. Use CONVERT_TIMEZONE('UTC','America/Los_Angeles', ts) before formatting to display local time, or add TIMESTAMPTZ in TO_CHAR patterns.

How do I display the weekday and hour for order analysis?

Example: TO_CHAR(o.order_date, 'Dy') AS weekday, TO_CHAR(o.order_date, 'HH24') AS hour quickly yields categorical fields for funnel or cohort charts.

Best practice checklist

1) Keep data typed, format only on SELECT. 2) Always quote the pattern string. 3) Use ISO YYYY-MM-DD for interchange. 4) Test edge cases (leap days, DST shifts).