Date/Time Data Types and Extraction

Working with Time

Dates and timestamps are everywhere: signup times, order dates, event logs, and session starts. If you’re doing any kind of analytics — retention, cohorts, time-series trends — you need to slice and dice temporal data. This module gives you the toolkit.

Fair warning: date functions are where SQL dialects diverge most. We cover all four — PostgreSQL, MySQL, SQL Server, and Oracle — throughout this module. The prose and examples use PostgreSQL syntax; the practice exercises include all dialects, so you can switch to your target database using the dialect selector above the editor.

Date/Time Data Types

Before extracting parts of dates, understand what you’re working with:

• DATE — Just the date: ‘2024-03-15’ (year, month, day only)
• TIME — Just the time: ‘14:30:00’ (hours, minutes, seconds)
• TIMESTAMP / DATETIME — Both date and time: ‘2024-03-15 14:30:00’
• TIMESTAMPTZ — Timestamp with timezone (PostgreSQL)

SELECT
  first_name,
  joining_date,
  EXTRACT(YEAR FROM joining_date) AS year,
  EXTRACT(MONTH FROM joining_date) AS month,
  EXTRACT(DAY FROM joining_date) AS day
FROM worker;

Common Extraction Values

• YEAR — The 4-digit year (2024)
• MONTH — Month number 1–12
• DAY — Day of month 1–31
• HOUR — Hour 0–23 (from timestamps)
• MINUTE — Minute 0–59
• DOW — Day of week (0=Sunday in PostgreSQL)
• WEEK — Week number of the year
• QUARTER — Quarter 1–4

Dialect Comparison: Date Extraction

• Current date:
  • PostgreSQL — CURRENT_DATE
  • MySQL — CURDATE()
  • MSSQL — CAST(GETDATE() AS DATE)
  • Oracle — TRUNC(SYSDATE)
• Current timestamp:
  • PostgreSQL — NOW()
  • MySQL — NOW()
  • MSSQL — GETDATE()
  • Oracle — SYSTIMESTAMP
• Extract year:
  • PostgreSQL — EXTRACT(YEAR FROM d)
  • MySQL — YEAR(d)
  • MSSQL — YEAR(d)
  • Oracle — EXTRACT(YEAR FROM d)
• Extract month:
  • PostgreSQL — EXTRACT(MONTH FROM d)
  • MySQL — MONTH(d)
  • MSSQL — MONTH(d)
  • Oracle — EXTRACT(MONTH FROM d)
• Extract day:
  • PostgreSQL — EXTRACT(DAY FROM d)
  • MySQL — DAY(d)
  • MSSQL — DAY(d)
  • Oracle — EXTRACT(DAY FROM d)
• Day of week:
  • PostgreSQL — EXTRACT(DOW FROM d)
  • MySQL — DAYOFWEEK(d)
  • MSSQL — DATEPART(dw, d)
  • Oracle — TO_CHAR(d, 'D')

Filtering by Date Parts

EXTRACT shines in WHERE clauses. Find employees who joined in January:

SELECT
  first_name,
  last_name,
  joining_date
FROM employees
WHERE EXTRACT(MONTH FROM joining_date) = 1;

Find all activities from 2023:

SELECT *
FROM events
WHERE EXTRACT(YEAR FROM event_date) = 2023;

Filtering by Month and Department

Use EXTRACT(MONTH) to filter for April (month 4) and LOWER() for case-insensitive department matching.

Combining with Other Functions

EXTRACT works seamlessly with aggregations. Count events by month:

Monthly Active Users

Use EXTRACT for the month and COUNT(DISTINCT) for unique users.

Day of Week Analysis

Finding patterns by day of week is a product analytics staple. Are users more active on weekends? Do signups spike on Mondays?

-- PostgreSQL: 0 = Sunday, 6 = Saturday
SELECT
  EXTRACT(DOW FROM created_at) AS day_of_week,
  COUNT(*) AS signups
FROM users
GROUP BY EXTRACT(DOW FROM created_at)
ORDER BY day_of_week;

Grouping by Year

Annual analysis is straightforward with EXTRACT:

SELECT
  EXTRACT(YEAR FROM order_date) AS year,
  SUM(total_order_cost) AS annual_revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;

Violations by Year

Use EXTRACT(YEAR) with GROUP BY to count violations per year.

Key Takeaways

• EXTRACT(part FROM date) pulls out year, month, day, hour, etc.
• Dialect syntax varies significantly — know your target database
• PostgreSQL — EXTRACT(); MySQL/MSSQL use YEAR(), MONTH(), DAY()
• Day of week numbering differs by database — always verify with test data
• EXTRACT works in SELECT, WHERE, GROUP BY, and ORDER BY

What’s Next

Extracting parts is just the beginning. Next, you’ll learn to perform date arithmetic: adding days, calculating differences between dates, and truncating to periods like weeks or months for time-series analysis.