SQL DATE_PART: Numeric Date Fields, Day of Week, and Dynamic Field by String

DATE_PART pulls a single numeric field out of a timestamp: the hour, the day of week, the week number, the seconds since the epoch. It is the function form of the EXTRACT operator, but here the field is a plain string, which means you can pass it dynamically.

What DATE_PART returns

The first argument is the field name as a string ('hour', 'dow', 'doy', 'week', 'month', 'epoch', and so on); the second is a timestamp or date. The result is always double precision, a number, not an interval or a string.

SELECT
  DATE_PART('hour',  TIMESTAMP '2026-06-17 14:32:09') AS hour,
  DATE_PART('dow',   TIMESTAMP '2026-06-17 14:32:09') AS day_of_week,
  DATE_PART('doy',   TIMESTAMP '2026-06-17 14:32:09') AS day_of_year,
  DATE_PART('week',  TIMESTAMP '2026-06-17 14:32:09') AS iso_week;
-- 14 | 3 | 168 | 25

Useful fields:

• 'hour', 'minute', 'second' are time components; 'second' includes the fractional part.
• 'year', 'month', 'day' are date components.
• 'dow' is the day of week, 0 = Sunday ... 6 = Saturday.
• 'doy' is the day of year, 1 to 366.
• 'week' is the ISO 8601 week number.
• 'epoch' is the number of seconds since 1970-01-01 00:00:00 UTC.

DATE_PART vs EXTRACT

EXTRACT('hour' FROM ts) and DATE_PART('hour', ts) produce the same number. The difference is syntactic: EXTRACT is an operator with a FROM keyword, while DATE_PART is an ordinary function.

-- These two lines are equivalent
SELECT EXTRACT(dow FROM created_at) FROM orders;
SELECT DATE_PART('dow', created_at) FROM orders;

The practical advantage of the function is that the field is a string literal, so it is easy to pass as a parameter. You can group orders by any date field without rewriting the query:

SELECT
  DATE_PART('hour', created_at) AS hour_of_day,
  COUNT(*)                      AS orders
FROM orders
WHERE status = 'paid'
GROUP BY DATE_PART('hour', created_at)
ORDER BY hour_of_day;

In PostgreSQL 14+, EXTRACT returns numeric while DATE_PART still returns double precision; for comparisons and ROUND that distinction occasionally matters.

Day of week: dow vs isodow

The biggest source of confusion is day numbering. 'dow' starts the week on Sunday and numbers it as zero; 'isodow' follows ISO, where Monday is 1 and Sunday is 7.

SELECT
  DATE_PART('dow',    DATE '2026-06-21') AS dow,     -- Sunday -> 0
  DATE_PART('isodow', DATE '2026-06-21') AS isodow;  -- Sunday -> 7

A typical "activity by weekday" report with a human-readable label:

SELECT
  DATE_PART('isodow', created_at) AS weekday_num,
  TO_CHAR(created_at, 'Dy')       AS weekday,
  COUNT(*)                        AS signups
FROM users
GROUP BY DATE_PART('isodow', created_at), TO_CHAR(created_at, 'Dy')
ORDER BY weekday_num;

Gotcha: if you filter weekends with DATE_PART('dow', d) IN (0, 6) and later switch to isodow, Saturday and Sunday become 6 and 7, so IN (0, 6) silently starts dropping Sunday. Always pin down which field you are using.

Epoch and time differences

The 'epoch' field turns a timestamp into seconds, and from an interval it extracts the full duration in seconds. It is a handy way to compute an age or a duration as a single number.

SELECT
  id,
  DATE_PART('epoch', NOW() - created_at) / 86400 AS account_age_days
FROM users
ORDER BY account_age_days DESC
LIMIT 10;

The same idea for the business metric "average order age in hours" grouped by employee department:

SELECT
  e.dept,
  AVG(DATE_PART('epoch', NOW() - o.created_at) / 3600) AS avg_order_age_hours
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN employees e ON e.name = u.name
GROUP BY e.dept;

Remember: DATE_PART('epoch', interval) returns the full seconds for an interval longer than a day, not a "part of a day", unlike 'hour', which on an interval returns only the hour component.

MySQL and ClickHouse differences

The name DATE_PART is not universal.

• MySQL has no DATE_PART. Use EXTRACT(HOUR FROM ts) or the dedicated functions HOUR(), DAYOFWEEK() (where Sunday is 1), DAYOFYEAR(), WEEK().

SELECT
  HOUR(created_at)      AS hour_of_day,
  DAYOFWEEK(created_at) AS dow_sun_is_1,
  COUNT(*)              AS orders
FROM orders
GROUP BY HOUR(created_at), DAYOFWEEK(created_at);

• ClickHouse offers toHour, toDayOfWeek (Monday is 1), toDayOfYear, and toUnixTimestamp for the epoch.

SELECT
  toHour(created_at)      AS hour_of_day,
  toDayOfWeek(created_at) AS dow_mon_is_1,
  count()                 AS orders
FROM orders
GROUP BY hour_of_day, dow_mon_is_1;

The intent is identical everywhere, pull a number out of a date, but each database numbers the weekday its own way, and that is the first thing to check when you port a query.