EXTRACT — Pull Parts Out of a Timestamp

-- Common parts
SELECT EXTRACT(YEAR  FROM NOW())     AS year,        -- 2025
       EXTRACT(MONTH FROM NOW())     AS month,       -- 3
       EXTRACT(DAY   FROM NOW())     AS day,         -- 12
       EXTRACT(HOUR  FROM NOW())     AS hour,
       EXTRACT(DOW   FROM NOW())     AS day_of_week, -- 0=Sunday … 6=Saturday
       EXTRACT(DOY   FROM NOW())     AS day_of_year, -- 1 … 366
       EXTRACT(WEEK  FROM NOW())     AS iso_week,
       EXTRACT(QUARTER FROM NOW())   AS quarter,
       EXTRACT(EPOCH FROM NOW())     AS unix_ts;     -- seconds since epoch

-- Group by hour-of-day across the whole table
SELECT EXTRACT(HOUR FROM created_at) AS hour_of_day,
       COUNT(*)                      AS event_count
FROM   events
GROUP  BY hour_of_day
ORDER  BY hour_of_day;

-- Filter by month (year-agnostic)
SELECT * FROM birthdays
WHERE  EXTRACT(MONTH FROM birth_date) = 3;

-- Difference in seconds (PostgreSQL — EXTRACT(EPOCH FROM interval))
SELECT EXTRACT(EPOCH FROM (NOW() - created_at)) AS seconds_old
FROM   sessions;

-- MySQL — same syntax works, plus DATE_FORMAT for arbitrary part extraction
SELECT YEAR(NOW()), MONTH(NOW()), DAYOFWEEK(NOW()), WEEK(NOW(), 3);