Группировка записей по дням в SQL: работа с timestamp

Быстрый ответ

Чтобы преобразовать временные метки в даты и сгруппировать события по дням, используйте функцию DATE() для извлечения даты:

SELECT DATE(timestamp) AS day, COUNT(*) AS event_count
FROM events
GROUP BY DATE(timestamp);

Этот запрос позволяет агрегировать данные по уникальным датам и посчитать количество событий для каждого дня.

Часовые пояса и форматы дат: подводные камни

Нормализация часового пояса

Если ваш проект ориентирован на международную аудиторию, необходимо учесть разницу во времени между часовыми поясами. Приведите временные метки к универсальному времени с помощью следующего способа:

-- Берегите природу! Переходим на Всемирное координированное время (UTC)! 
SET time_zone = '+00:00';

SELECT DATE(CONVERT_TZ(timestamp, @@session.time_zone, '+00:00')) AS day_utc, COUNT(*) AS event_count
FROM events
GROUP BY day_utc;

Подробнее об этом расскажет наш спикер на видео

Дружественный формат вывода дат

Отображайте даты в пользовательском формате. Для этого используйте функцию DATE_FORMAT():

SELECT DATE_FORMAT(DATE(timestamp), '%d/%m/%Y') AS formatted_day, COUNT(*) AS event_count
FROM events
GROUP BY formatted_day;

Встреча с UNIX

UNIX-время основано на целочисленных значениях и требует особой функции для преобразования, такой как FROM_UNIXTIME():

SELECT DATE(FROM_UNIXTIME(unix_timestamp)) AS day, COUNT(*) AS event_count
FROM events
GROUP BY day;

Распределение рангов по команде

Для распределения рейтинга постов внутри каждого дня используйте ROW_NUMBER():

SELECT DATE(timestamp) AS day,
       ROW_NUMBER() OVER(PARTITION BY DATE(timestamp) ORDER BY timestamp) AS rank,
       content
FROM posts;

Визуализация категорий временных меток

Представьте календарь как систему ячеек и временные метки как посетителей, которые выбирают подходящий день для "посещения":

Посетители Время прибытия Номер ячейки Время "размещения"
  🕒      12-03 08:33          🚪 12-03
  🕓      12-03 19:20          🚪 12-03
  🕔      12-04 03:15          🚪 12-04
  🕕      12-05 14:48          🚪 12-05

Группировка по датам с помощью GROUP BY:

🕒🕓 ➡️ 🚪 12-03  // Посетители, приехавшие в один день, размещаются в одной ячейке
🕔 ➡️ 🚪 12-04
🕕 ➡️ 🚪 12-05

Каждая ячейка представляет уникальную дату, что упрощает анализ событий.

Зен и мастерство группировки в SQL

Фильтрация: на пути к ясности

Сочетайте GROUP BY с условием WHERE для фильтрации и упорядочивания данных:

SELECT DATE(timestamp) AS day, COUNT(*) AS event_count
FROM events
WHERE timestamp >= '2023-01-01' AND timestamp < '2023-02-01'
GROUP BY day;

Этот запрос позволит вам просмотерть только события, произошедшие в январе 2023 года.

Упорядочивание данных

Упорядочивайте данные по дате или количеству событий, чтобы обеспечить наглядность:

SELECT DATE(timestamp) AS day, COUNT(*) AS event_count
FROM events
GROUP BY day
ORDER BY day DESC; -- или можно использовать ORDER BY event_count DESC

Сортировка упрощает восприятие данных и облегчает работу.

Фильтрация по интервалам времени

Развивайте свои навыки, устанавливая свои временные интервалы для группировки:

-- Каждый час – это новое приключение в SQL!
SELECT DATE(DATE_ADD(timestamp, INTERVAL 1-HOUR(timestamp) HOUR)) AS start_of_hour,
       COUNT(*) AS event_count
FROM events
GROUP BY start_of_hour;

Такой подход делает каждый час дня особенным, что заслуживает отдельного поздравления!

Полезные материалы

1. PostgreSQL: Документация: 9.9. Функции и операторы даты/времени — основы работы с функциями даты и времени в PostgreSQL.
2. Группировка по интервалу в 5 минут в заданном временном диапазоне – Stack Overflow — пример группировки по временным интервалам в MySQL.
3. Primefaces: commandButton в confirmDialog не может обновить datatable в той же форме – Stack Overflow — несмотря на название, этот вопрос касается группировки по датам в SQL Server.
4. SQL Sentry | SolarWinds — как использовать функции даты и времени для мониторинга производительности SQL Server.
5. Функции даты и времени – База знаний MariaDB — справочник функций даты и времени MariaDB для группировки данных по датам в SQL.
6. SQL GROUP BY | Продвинутый SQL – Mode — подробное руководство по продвинутому использованию оператора GROUP BY.