DATE_TRUNC

1
SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-15 14:30:00');

1
SELECT
2
  DATE_TRUNC('month', order_date) as month,
3
  COUNT(*) as total_orders,
4
  SUM(amount) as total_revenue
5
FROM orders
6
GROUP BY DATE_TRUNC('month', order_date)
7
ORDER BY month;

1
SELECT
2
  DATE_TRUNC('week', created_at) as week_start,
3
  COUNT(DISTINCT user_id) as weekly_active_users
4
FROM user_sessions
5
GROUP BY 1
6
ORDER BY 1 DESC
7
LIMIT 10;

1
SELECT
2
  DATE_TRUNC('hour', event_time) as hour,
3
  COUNT(*) as events_per_hour
4
FROM events
5
WHERE event_date = CURRENT_DATE
6
GROUP BY 1
7
ORDER BY 1;