Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

04

Chapter 4

Date & Time Functions

Time, done properly. Intervals, truncation, extraction, time zones, and the arithmetic that survives DST.

In this chapter

16

queries, easy → advanced

  1. 001CURRENT_DATE, NOW(), CURRENT_TIMESTAMPRead
  2. 002Date arithmetic: add/subtract intervalsRead
  3. 003EXTRACT: get date partsRead
  4. 004DATE_TRUNC: truncate to time periodRead
  5. 005AGE: calculate time differenceRead
  6. 006TO_CHAR: format dates as stringsRead
  7. 007Generate series of dates for calendar queriesRead
  8. 008Time zone conversion and handlingRead
  9. 009Business days calculation (excluding weekends)Read
  10. 010Show orders from last 7 days, last 30 days, last 90 daysRead
  11. 011Show age of each customer accountRead
  12. 012Find the busiest day of week for ordersRead
  13. 013Calculate SLA breach: orders not shipped in 3 daysRead
  14. 014Find employees with work anniversaries this monthRead
  15. 015Calculate business hours between two timestampsRead
  16. 016Partition by date range with automatic partition creationRead