Formatting and Parsing Dates

Learning Focus

Use this lesson to format dates/timestamps with to_char, parse strings with to_date/to_timestamp, and choose safe casts (::date, ::timestamptz) for PostgreSQL.

Concept Overview

Formatting and parsing are two sides of the same problem:

• Formatting converts a date/time value into text for display or exports.
• Parsing converts text into a date/time value you can compare, index, and compute with.

In PostgreSQL, you typically format with to_char and parse with:

• typed literals / casts (DATE '2026-03-05', '2026-03-05T12:00:00Z'::timestamptz)
• to_date(text, format)
• to_timestamp(text, format)

Why is it important?

• Correct comparisons: parse into date/timestamptz before filtering
• Index usage: avoid wrapping indexed columns in formatting functions
• Interoperability: produce ISO 8601 strings for APIs
• Timezone safety: preserve absolute time when required

Where does it fit?

You use formatting and parsing in:

• reports and dashboards
• imports (CSV, JSON payloads)
• API serialization (ISO timestamps)
• migration scripts that normalize legacy text columns

---

Syntax & Rules

Core Syntax

Format values:

SELECT to_char(TIMESTAMPTZ '2026-03-05 12:00+00', 'YYYY-MM-DD"T"HH24:MI:SSOF');

Parse values:

SELECT to_date('2026-03-05', 'YYYY-MM-DD');
SELECT to_timestamp('2026-03-05 12:30:00', 'YYYY-MM-DD HH24:MI:SS');
SELECT '2026-03-05T12:30:00Z'::timestamptz;

Common Format Tokens (to_char / to_timestamp)

Token	Meaning	Example
YYYY	4-digit year	2026
MM	month (01-12)	03
DD	day (01-31)	05
HH24	hour (00-23)	14
MI	minute (00-59)	07
SS	second (00-59)	09
MS	milliseconds	123
OF	UTC offset (+08, -05)	+00
TZ / TZH:TZM	timezone name / offset pieces	depends
Dy / Mon	abbreviated day/month	Wed, Mar

Available Options / Parameters

Function	Signature	Notes
to_char	to_char(value, format)	returns text; for display/exports
to_date	to_date(text, format)	returns date
to_timestamp	to_timestamp(text, format)	returns timestamptz
Cast	text::date, text::timestamptz	best for ISO-like inputs

Key Rules and Considerations

• Prefer parsing to a typed value before comparisons.
• Avoid to_char(column, ...) in WHERE clauses; it can prevent index usage.
• For ISO 8601 with timezone, ::timestamptz is often simplest.
• to_timestamp(text, format) returns timestamptz (timezone-aware). Set session timezone if you need consistent display.

---

Step-by-Step Examples

Example 1: Format a Timestamp as ISO 8601 (Beginner)

SELECT to_char(
  TIMESTAMPTZ '2026-03-05 12:00:01+00',
  'YYYY-MM-DD"T"HH24:MI:SSOF'
) AS iso8601;

Expected output:

         iso8601
--------------------------
 2026-03-05T12:00:01+00
(1 row)

Explanation:

• OF prints the UTC offset.
• Quoting "T" inserts a literal T.

---

Example 2: Parse Text into a Date (Beginner)

SELECT to_date('2026-03-05', 'YYYY-MM-DD') AS d;

Expected output:

     d
------------
 2026-03-05
(1 row)

Explanation:

• to_date is useful when you know the exact format of the incoming string.

---

Example 3: Parse Text into a Timestamp (Intermediate)

SELECT to_timestamp(
  '2026-03-05 18:30:00',
  'YYYY-MM-DD HH24:MI:SS'
) AS ts;

Expected output (example):

           ts
-----------------------------
 2026-03-05 18:30:00+00
(1 row)

Explanation:

• to_timestamp(text, format) returns a timestamptz.
• The displayed offset depends on your session timezone.

---

Example 4: Prefer Casts for ISO Inputs (Intermediate)

SELECT
  '2026-03-05'::date AS d,
  '2026-03-05T12:00:00Z'::timestamptz AS ts_utc;

Expected output:

     d      |        ts_utc
------------+------------------------
 2026-03-05 | 2026-03-05 12:00:00+00
(1 row)

Explanation:

• Typed casts are readable and fast for standard formats.

---

Example 5: Keep Index Usage When Filtering by Date (Advanced)

Setup:

DROP TABLE IF EXISTS events;

CREATE TABLE events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL,
  payload text NOT NULL
);

INSERT INTO events (created_at, payload) VALUES
  (TIMESTAMPTZ '2026-03-05 00:05+00', 'a'),
  (TIMESTAMPTZ '2026-03-05 12:00+00', 'b'),
  (TIMESTAMPTZ '2026-03-06 00:01+00', 'c');

CREATE INDEX idx_events_created_at ON events (created_at);

Good (range filter preserves index usage):

SELECT event_id
FROM events
WHERE created_at >= TIMESTAMPTZ '2026-03-05 00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-03-06 00:00+00'
ORDER BY event_id;

Expected output:

 event_id
----------
 1
 2
(2 rows)

Explanation:

• This avoids to_char(created_at, ...) or created_at::date in the predicate.

---

Practical Use Cases

1) API Output Formatting

Context: produce ISO strings.

SELECT to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF');

2) Parsing Dates from Imports

Context: CSV import provides MM/DD/YYYY.

SELECT to_date('03/05/2026', 'MM/DD/YYYY');

3) Parsing Epoch Seconds

Context: event time arrives as epoch seconds.

SELECT to_timestamp(1772683200) AS ts;

4) Building Normalized Date Keys

Context: generate a YYYYMMDD key.

SELECT to_char(DATE '2026-03-05', 'YYYYMMDD') AS date_key;

---

Common Mistakes & Troubleshooting

Mistake 1: Formatting in WHERE Clauses

Wrong (often prevents index usage):

SELECT *
FROM events
WHERE to_char(created_at, 'YYYY-MM-DD') = '2026-03-05';

Fix (use typed range):

SELECT *
FROM events
WHERE created_at >= TIMESTAMPTZ '2026-03-05 00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-03-06 00:00+00';

---

Mistake 2: Losing Time Zone Information

What happens: parsing into timestamp (no tz) can shift meaning.

Fix: use timestamptz for event times, and parse ISO strings with ::timestamptz when possible.

---

Mistake 3: Incorrect Format Strings

What happens: to_timestamp parses wrong values or errors.

Fix: verify the incoming format matches the pattern exactly.

Debugging tips:

1. First SELECT the raw text and a parsed attempt side-by-side.
2. Confirm session timezone if results look offset.
3. Prefer typed literals/casts for standard formats.
4. Keep parsing at ingestion; store normalized date/timestamptz in the table.

---

Best Practices

• ✅ Store typed date/timestamptz; ❌ store dates as text.
• ✅ Use to_char for output only; ❌ use it for filtering.
• ✅ Prefer casts for ISO strings; ❌ overuse to_timestamp when a cast is clearer.
• ✅ Use range predicates for day filters; ❌ cast columns to date in predicates on large tables.
• ✅ Standardize on UTC for storage and convert for display; ❌ mix time zones in storage.

---

Hands-On Practice

Use this setup for the exercises:

DROP TABLE IF EXISTS practice_events;

CREATE TABLE practice_events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL
);

INSERT INTO practice_events (created_at) VALUES
  (TIMESTAMPTZ '2026-03-05 10:00+00'),
  (TIMESTAMPTZ '2026-03-06 10:00+00');

Exercise 1 (Easy): Format a timestamp

Task: Format TIMESTAMPTZ '2026-03-05 10:00+00' as YYYY-MM-DD.

-- Your SQL here

Solution:

SELECT to_char(TIMESTAMPTZ '2026-03-05 10:00+00', 'YYYY-MM-DD') AS d;

---

Exercise 2 (Medium): Parse a date string

Task: Parse 03/05/2026 using the correct pattern.

-- Your SQL here

Solution:

SELECT to_date('03/05/2026', 'MM/DD/YYYY') AS d;

---

Exercise 3 (Advanced): Filter by day using a range

Task: Return event ids for events on 2026-03-05 using a range filter.

-- Your SQL here

Solution:

SELECT event_id
FROM practice_events
WHERE created_at >= TIMESTAMPTZ '2026-03-05 00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-03-06 00:00+00'
ORDER BY event_id;

---

Connection to Other Concepts

Concept	Why it matters
Current time functions	determine what "now" means when formatting output
Indexes	formatting in predicates can block index usage
Date arithmetic	parsed values enable correct interval math
JSONB	APIs often carry ISO timestamps in JSON payloads
ETL	parsing should happen once at ingestion, not repeatedly at query time

---

Visual Learning Diagram

flowchart TD
  A[Text input\n"03/05/2026"] --> B[to_date/to_timestamp]
  B --> C[Typed value\ndate/timestamptz]
  C --> D[Compare + Index]
  C --> E[to_char formatting]
  E --> F[Text output\n"2026-03-05"]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F allNodes
  class C highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Formatting in predicates	slow queries	use typed range comparisons
Parsing into timestamp unintentionally	timezone bugs	use timestamptz for events
Wrong format tokens	wrong parsed values	test with known inputs
Storing dates as text	hard comparisons	store date / timestamptz
Repeated parsing	wasted CPU	parse once at ingestion

---

Quick Reference

SELECT to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF')
SELECT to_date('2026-03-05', 'YYYY-MM-DD')
SELECT to_timestamp('2026-03-05 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
SELECT '2026-03-05T12:00:00Z'::timestamptz
SELECT to_timestamp(1772683200)

What's Next

• Previous: Current Date and Time Functions - Choose correct "now" semantics.
• Next: Date Arithmetic and Difference - Add/subtract intervals and compute deltas.
• Module Overview - Return to the Date and Time Functions index.