Can my DB?

-- Truncate to month start
SELECT DATE_TRUNC('month', created_at) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- Truncate to week start
SELECT DATE_TRUNC('week', event_at) AS week_start
FROM events;

-- With timezone (truncates in that timezone, avoids DST issues)
SELECT DATE_TRUNC('day', event_at, 'America/New_York') AS day_start
FROM events;

• Units: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.
• Returns the same type as input (timestamptz or timestamp).
• Optional third argument specifies the timezone in which to truncate, needed for DST-aware day/week truncation.
• Week truncation uses ISO week (Monday as first day).

-- Truncate to month start (workaround)
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- Truncate to day
SELECT DATE(created_at) AS day_start FROM orders;

-- Truncate to hour
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS hour_start
FROM orders;

• No DATE_TRUNC function exists in MySQL.
• DATE(ts) is the simplest workaround for day truncation.
• DATE_FORMAT(ts, format) rebuilds the date with zeroed components for month/hour/etc.
• STR_TO_DATE(DATE_FORMAT(ts, '%Y-%m-01'), '%Y-%m-%d') returns a proper DATE type.
• Returns strings, not date/time types, for most workarounds - cast as needed.

-- DATE_TRUNC (MariaDB 10.3.7+)
SELECT DATE_TRUNC('month', created_at) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- Pre-10.3.7 workaround
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month_start
FROM orders;

• DATE_TRUNC added in MariaDB 10.3.7.
• Same units and semantics as PostgreSQL.
• Prior to 10.3.7, the same DATE_FORMAT workarounds as MySQL are required.

-- Truncate to month start
SELECT TRUNC(order_date, 'MM') AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY TRUNC(order_date, 'MM')
ORDER BY 1;

-- Common format codes
-- 'YYYY'  -- year
-- 'MM'    -- month
-- 'DD'    -- day (default)
-- 'HH24'  -- hour
-- 'MI'    -- minute
-- 'IW'    -- ISO week
-- 'Q'     -- quarter

SELECT TRUNC(SYSDATE, 'YYYY') AS year_start,
       TRUNC(SYSDATE, 'Q')    AS quarter_start
FROM DUAL;

• TRUNC(date, format_code) uses format codes that match TO_CHAR/TO_DATE mask syntax.
• Format codes: YYYY (year), Q (quarter), MM (month), IW (ISO week), DD (day), HH24 (hour), MI (minute).
• Oracle 23ai also recognizes DATE_TRUNC as an alias for compatibility.
• Works on both DATE and TIMESTAMP types; TIMESTAMP result preserves fractional seconds context.

-- DATETRUNC (SQL Server 2022+)
SELECT DATETRUNC(month, created_at) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY DATETRUNC(month, created_at)
ORDER BY 1;

-- Pre-2022 workaround for month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, created_at), 0) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY DATEADD(month, DATEDIFF(month, 0, created_at), 0)
ORDER BY 1;

• DATETRUNC() added in SQL Server 2022.
• Units: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond.
• Pre-2022 workaround: DATEADD(month, DATEDIFF(month, 0, col), 0) for month; DATEADD(day, DATEDIFF(day, 0, col), 0) for day.
• DATETRUNC is not available in Azure SQL Database older than compatibility level 160.

-- Truncate to month start
SELECT strftime('%Y-%m-01', created_at) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- Truncate to day
SELECT date(created_at) AS day_start FROM orders;

-- Truncate to hour
SELECT strftime('%Y-%m-%d %H:00:00', created_at) AS hour_start
FROM orders;

• No DATE_TRUNC function.
• Use strftime() to rebuild the date/time string with zeroed components.
• date(ts) is a convenient shorthand for day-level truncation.
• Results are strings, not date/time types - treat accordingly in comparisons.
• No support for week truncation without manual calculation.

SELECT DATE_TRUNC('month', created_at) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

• DATE_TRUNC has the same syntax and semantics as PostgreSQL.
• Units: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.
• Works with both TIMESTAMP and TIMESTAMPTZ types.
• Redshift inherits much of PostgreSQL's date/time function API.

-- TRUNC for DATE truncation
SELECT TRUNC(order_date, 'MM') AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY TRUNC(order_date, 'MM')
ORDER BY 1;

-- TRUNC_TIMESTAMP for timestamp truncation
SELECT TRUNC_TIMESTAMP(created_at, 'MM') AS month_start
FROM orders;

-- DATE_TRUNC (newer DB2 versions)
SELECT DATE_TRUNC('month', created_at) FROM orders;

• TRUNC function works for DATE truncation using Oracle-compatible format codes.
• TRUNC_TIMESTAMP handles timestamp truncation.
• DATE_TRUNC available in newer DB2 versions (11.5.x with specific fix packs).
• Format codes align with Oracle's: 'YYYY', 'MM', 'DD', 'HH', 'MI', 'SS'.

-- DATE_TRUNC
SELECT DATE_TRUNC('month', created_at) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- TRUNC as alias
SELECT TRUNC(created_at, 'month') FROM orders;

-- Works on DATE, TIME, and TIMESTAMP types
SELECT DATE_TRUNC('hour', CURRENT_TIMESTAMP);

• DATE_TRUNC is the canonical spelling; TRUNC(date, unit) works as an alias.
• Supports all standard units: year, quarter, month, week, day, hour, minute, second, etc.
• Works on DATE, TIME, TIMESTAMP_NTZ, TIMESTAMP_LTZ, and TIMESTAMP_TZ types.
• Week truncation uses Sunday as the first day of the week by default (configurable via WEEK_START parameter).

-- DATE_TRUNC for DATE type
SELECT DATE_TRUNC(order_date, MONTH) AS month_start,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- DATETIME_TRUNC for DATETIME type
SELECT DATETIME_TRUNC(created_at, MONTH) AS month_start
FROM orders;

-- TIMESTAMP_TRUNC with timezone
SELECT TIMESTAMP_TRUNC(event_at, DAY, 'America/New_York') AS day_start
FROM events;

• Three separate functions for different types: DATE_TRUNC, DATETIME_TRUNC, TIMESTAMP_TRUNC.
• Unit is an unquoted keyword (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND), not a string.
• TIMESTAMP_TRUNC accepts an optional timezone parameter for local-time truncation.
• Week truncation: WEEK starts Sunday; WEEK(MONDAY) through WEEK(SUNDAY) available for ISO week compatibility.