Как работать с датами в SQL (DATE_TRUNC, EXTRACT, INTERVAL)?

Работа с датами в SQL

Даты — неотъемлемая часть аналитики. SQL предоставляет мощные функции для работы с временем: DATE_TRUNC для группировки, EXTRACT для выделения частей даты, INTERVAL для расчетов.

DATE_TRUNC — обрезание дат

FUNKCIYA DATE_TRUNC обрезает дату до указанной единицы времени (год, месяц, день, час и т.д.).

-- Обрезать до начала месяца
SELECT DATE_TRUNC('month', order_date) AS month_start
FROM orders;

-- Результат: 2024-01-01, 2024-02-01, 2024-03-01

-- Группировка продаж по месяцам
SELECT 
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS orders_count,
  SUM(amount) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Группировка по неделям
SELECT 
  DATE_TRUNC('week', order_date) AS week_start,
  SUM(amount) AS weekly_revenue
FROM orders
GROUP BY DATE_TRUNC('week', order_date);

EXTRACT — извлечение части даты

EXTRACT выделяет конкретную часть даты: год, месяц, день, час, минуту и т.д.

-- Извлечение разных частей
SELECT 
  order_date,
  EXTRACT(YEAR FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  EXTRACT(DAY FROM order_date) AS day,
  EXTRACT(QUARTER FROM order_date) AS quarter,
  EXTRACT(DOW FROM order_date) AS day_of_week,
  EXTRACT(HOUR FROM created_at) AS hour
FROM orders;

-- Фильтр по году
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

-- Фильтр по месяцу (независимо от года)
SELECT * FROM orders
WHERE EXTRACT(MONTH FROM order_date) IN (1, 12);  -- январь и декабрь

-- Аналитика по дню недели
SELECT 
  CASE EXTRACT(DOW FROM order_date)
    WHEN 0 THEN 'Воскресенье'
    WHEN 1 THEN 'Понедельник'
    WHEN 2 THEN 'Вторник'
    WHEN 3 THEN 'Среда'
    WHEN 4 THEN 'Четверг'
    WHEN 5 THEN 'Пятница'
    WHEN 6 THEN 'Суббота'
  END AS day_name,
  COUNT(*) AS orders_count
FROM orders
GROUP BY EXTRACT(DOW FROM order_date);

INTERVAL — операции со временем

INTERVAL добавляет или вычитает время из дат.

-- Добавить дни
SELECT 
  order_date,
  order_date + INTERVAL '7 days' AS delivery_date,
  order_date + INTERVAL '30 days' AS return_deadline
FROM orders;

-- Вычесть месяцы
SELECT 
  CURRENT_DATE - INTERVAL '3 months' AS three_months_ago;

-- Расчет времени жизни акаунта
SELECT 
  user_id,
  created_at,
  CURRENT_DATE - created_at::DATE AS days_active
FROM users;

-- Поиск событий за последние 30 дней
SELECT * FROM events
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days';

-- Добавить часы
SELECT 
  created_at,
  created_at + INTERVAL '2 hours' AS deadline
FROM support_tickets;

Комбинированные примеры

Пример 1: Анализ по датам

SELECT 
  DATE_TRUNC('month', order_date)::DATE AS month,
  EXTRACT(DOW FROM order_date) AS day_of_week,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY DATE_TRUNC('month', order_date), EXTRACT(DOW FROM order_date)
ORDER BY month DESC;

Пример 2: RFM анализ (дни с последней покупки)

SELECT 
  user_id,
  MAX(order_date) AS last_order,
  CURRENT_DATE - MAX(order_date)::DATE AS days_since_purchase,
  COUNT(*) AS total_orders
FROM orders
GROUP BY user_id
HAVING CURRENT_DATE - MAX(order_date)::DATE > 90  -- неактивны более 90 дней
ORDER BY last_order DESC;

Пример 3: Когортный анализ

SELECT 
  DATE_TRUNC('month', created_at)::DATE AS cohort_month,
  DATE_TRUNC('month', order_date)::DATE AS order_month,
  COUNT(DISTINCT user_id) AS users
FROM orders
JOIN users ON orders.user_id = users.id
GROUP BY DATE_TRUNC('month', created_at), DATE_TRUNC('month', order_date)
ORDER BY cohort_month, order_month;

Важные моменты

• DATE_TRUNC возвращает timestamp, используй ::DATE для преобразования
• EXTRACT возвращает число (день недели: 0-6, месяц: 1-12)
• INTERVAL поддерживает: day, week, month, year, hour, minute, second
• CURRENT_DATE — сегодня, CURRENT_TIMESTAMP — текущее время
• Приводи временные зоны: AT TIME ZONE 'Europe/Moscow'

Эти функции — основа временных анализов в SQL.