Date and Time Functions

• Now/current time:
  • PostgreSQL: NOW(), CURRENT_DATE, CURRENT_TIMESTAMP
  • MySQL: NOW(), CURDATE()
  • SQL Server: SYSDATETIME(), GETDATE(), CAST(GETDATE() AS date)
  • BigQuery: CURRENT_DATE(), CURRENT_TIMESTAMP()
  • SQLite: datetime('now'), date('now')
• Extract parts:
  • PostgreSQL: EXTRACT(YEAR FROM ts), DATE_PART('dow', ts)
  • MySQL: YEAR(ts), MONTH(ts), DAYOFWEEK(ts)
  • SQL Server: DATEPART(year, ts), DATEPART(week, ts)
  • BigQuery: EXTRACT(YEAR FROM ts)
  • SQLite: strftime('%Y', ts)
• Truncate/bucket:
  • PostgreSQL: DATE_TRUNC('day', ts), DATE_TRUNC('week', ts)
  • MySQL: DATE(ts) (day), or use DATE_FORMAT for formatted; week bucketing often via YEARWEEK(ts)
  • SQL Server: CAST(ts AS date) (day), or build week starts with DATEADD/DATEDIFF
  • BigQuery: DATE_TRUNC(date_col, WEEK), TIMESTAMP_TRUNC(ts, DAY)
  • SQLite: date(ts) (day); weeks via custom calculations
• Add/subtract intervals:
  • PostgreSQL: ts + INTERVAL '7 days'
  • MySQL: DATE_ADD(ts, INTERVAL 7 DAY)
  • SQL Server: DATEADD(day, 7, ts)
  • BigQuery: TIMESTAMP_ADD(ts, INTERVAL 7 DAY)
  • SQLite: datetime(ts, '+7 days')
• Differences:
  • PostgreSQL: EXTRACT(EPOCH FROM (t2 - t1)) / 3600 AS hours
  • MySQL: TIMESTAMPDIFF(HOUR, t1, t2)
  • SQL Server: DATEDIFF(hour, t1, t2)
  • BigQuery: TIMESTAMP_DIFF(t2, t1, HOUR)
  • SQLite: (julianday(t2) - julianday(t1)) * 24 AS hours
• Reliable date ranges: prefer half-open intervals: created_at >= start AND created_at < next_boundary to avoid missing late-night times.

Note: Syntax varies by database; adjust to your system.

Assume table: events(user_id, occurred_at TIMESTAMP).

-- PostgreSQL
SELECT DATE_TRUNC('day', occurred_at) AS day_start,
       COUNT(DISTINCT user_id) AS dau
FROM events
WHERE occurred_at >= DATE_TRUNC('day', NOW()) - INTERVAL '7 days'
  AND occurred_at < DATE_TRUNC('day', NOW())
GROUP BY 1
ORDER BY 1;

-- MySQL
SELECT DATE(occurred_at) AS day_start,
       COUNT(DISTINCT user_id) AS dau
FROM events
WHERE occurred_at >= DATE(NOW()) - INTERVAL 7 DAY
  AND occurred_at < DATE(NOW())
GROUP BY 1
ORDER BY 1;

-- SQL Server
WITH today AS (
  SELECT CAST(GETDATE() AS date) AS d
)
SELECT CAST(occurred_at AS date) AS day_start,
       COUNT(DISTINCT user_id) AS dau
FROM events, today
WHERE occurred_at >= today.d AND occurred_at < DATEADD(day, 0, today.d)
  AND occurred_at >= DATEADD(day, -7, today.d)
  AND occurred_at < today.d
GROUP BY CAST(occurred_at AS date)
ORDER BY day_start;

-- BigQuery
SELECT TIMESTAMP_TRUNC(occurred_at, DAY) AS day_start,
       COUNT(DISTINCT user_id) AS dau
FROM `project.dataset.events`
WHERE occurred_at >= TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) - INTERVAL 7 DAY
  AND occurred_at < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
GROUP BY 1
ORDER BY 1;

Assume table: orders(id, created_at TIMESTAMP).

-- PostgreSQL (weeks start Monday by default)
SELECT DATE_TRUNC('week', created_at)::date AS week_start,
       COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

-- MySQL (week start can vary; mode 3 = Monday start)
SELECT STR_TO_DATE(CONCAT(YEARWEEK(created_at, 3), ' Monday'), '%X%V %W') AS week_start,
       COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

-- SQL Server (compute Monday week start)
SELECT DATEADD(day, - (DATEPART(weekday, created_at) + 5) % 7, CAST(created_at AS date)) AS week_start,
       COUNT(*) AS orders
FROM orders
GROUP BY DATEADD(day, - (DATEPART(weekday, created_at) + 5) % 7, CAST(created_at AS date))
ORDER BY week_start;

-- BigQuery
SELECT DATE_TRUNC(DATE(created_at), WEEK(MONDAY)) AS week_start,
       COUNT(*) AS orders
FROM `project.dataset.orders`
GROUP BY 1
ORDER BY 1;

Assume table: shipments(order_id, shipped_at TIMESTAMP, delivered_at TIMESTAMP).

-- PostgreSQL
SELECT AVG(EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 3600.0) AS avg_hours
FROM shipments
WHERE shipped_at IS NOT NULL AND delivered_at IS NOT NULL;

-- MySQL
SELECT AVG(TIMESTAMPDIFF(HOUR, shipped_at, delivered_at)) AS avg_hours
FROM shipments
WHERE shipped_at IS NOT NULL AND delivered_at IS NOT NULL;

-- SQL Server
SELECT AVG(CAST(DATEDIFF(hour, shipped_at, delivered_at) AS float)) AS avg_hours
FROM shipments
WHERE shipped_at IS NOT NULL AND delivered_at IS NOT NULL;

-- BigQuery
SELECT AVG(TIMESTAMP_DIFF(delivered_at, shipped_at, HOUR)) AS avg_hours
FROM `project.dataset.shipments`
WHERE shipped_at IS NOT NULL AND delivered_at IS NOT NULL;