参考資料：FLOOR関数、DATEPART関数、CAST関数、CONVERT関数、DATEADD関数、DATEDIFF関数、CASE式

SQL Serverで日付時刻列を時間考慮せずにグループ化する

SQL Serverで日付時刻列をグループ化する際、時間情報も考慮すると、グループ数が多くなり、分析が複雑になる場合があります。そこで、時間情報を無視してグループ化する方法を紹介します。

方法

以下の2つの方法があります。

FLOOR関数は、指定された精度で数値を切り捨てます。日付時刻列に対してFLOOR関数を使うことで、時間情報を切り捨てて日付のみでグループ化できます。

SELECT FLOOR(date_time_column) AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

DATEPART関数は、日付時刻列から指定された日付要素を抽出します。DATEPART関数を使って、日付時刻列から時間要素のみを抽出 and 0 を加算することで、時間情報を無視して日付のみでグループ化できます。

SELECT DATEPART(dd, date_time_column) + DATEPART(mm, date_time_column) * 100 + DATEPART(yy, date_time_column) * 10000 AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

例

以下のテーブルがあるとします。

CREATE TABLE table_name (
  id INT,
  date_time_column DATETIME
);

INSERT INTO table_name (id, date_time_column) VALUES
  (1, '2024-03-27 10:00:00'),
  (2, '2024-03-27 11:00:00'),
  (3, '2024-03-28 12:00:00'),
  (4, '2024-03-28 13:00:00');

SELECT FLOOR(date_time_column) AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

結果

date_only | count
----------+-------
2024-03-27 | 2
2024-03-28 | 2

SELECT DATEPART(dd, date_time_column) + DATEPART(mm, date_time_column) * 100 + DATEPART(yy, date_time_column) * 10000 AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

date_only | count
----------+-------
20240327 | 2
20240328 | 2

上記の2つの方法を使うことで、SQL Serverで日付時刻列を時間考慮せずにグループ化できます。

補足

• 上記の例では、日付のみでグループ化していますが、月や年など、他の日付要素でグループ化することもできます。
• GROUP BY 句で複数の列を指定することで、複数の列でグループ化できます。

CREATE TABLE table_name (
  id INT,
  date_time_column DATETIME
);

INSERT INTO table_name (id, date_time_column) VALUES
  (1, '2024-03-27 10:00:00'),
  (2, '2024-03-27 11:00:00'),
  (3, '2024-03-28 12:00:00'),
  (4, '2024-03-28 13:00:00'),
  (5, '2024-03-29 14:00:00'),
  (6, '2024-03-29 15:00:00'),
  (7, '2024-03-30 16:00:00'),
  (8, '2024-03-30 17:00:00');

方法1：FLOOR関数を使う

SELECT FLOOR(date_time_column) AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

date_only | count
----------+-------
2024-03-27 | 2
2024-03-28 | 2
2024-03-29 | 2
2024-03-30 | 2

方法2：DATEPART関数を使う

SELECT DATEPART(dd, date_time_column) + DATEPART(mm, date_time_column) * 100 + DATEPART(yy, date_time_column) * 10000 AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

date_only | count
----------+-------
20240327 | 2
20240328 | 2
20240329 | 2
20240330 | 2

方法3：GROUP BY 句で複数の列を指定する

SELECT YEAR(date_time_column) AS year, MONTH(date_time_column) AS month, COUNT(*) AS count
FROM table_name
GROUP BY YEAR(date_time_column), MONTH(date_time_column)
ORDER BY YEAR(date_time_column), MONTH(date_time_column);

year | month | count
-----+-------+-------
2024 | 3    | 8

• 上記のサンプルコードは、SQL Server 2019で動作確認しています。
• 他のデータベースを使用する場合は、構文が異なる場合があります。

日付時刻列を時間考慮せずにグループ化する他の方法

CAST関数は、データ型を変換します。日付時刻列を文字列型に変換することで、時間情報を無視してグループ化できます。

SELECT CAST(date_time_column AS DATE) AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

SELECT CONVERT(VARCHAR(10), date_time_column, 120) AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

SELECT DATEADD(date_time_column, -HOUR(date_time_column), HOUR) AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

SELECT DATEDIFF(date_time_column, '00:00:00') AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

T-SQL の CASE 式を使うことで、日付時刻列の値に基づいて異なる値を返すことができます。CASE 式を使って、時間情報に基づいて異なる値を返すことで、時間情報を無視してグループ化できます。

SELECT CASE
  WHEN HOUR(date_time_column) BETWEEN 0 AND 5 THEN '00:00:00'
  WHEN HOUR(date_time_column) BETWEEN 6 AND 11 THEN '06:00:00'
  WHEN HOUR(date_time_column) BETWEEN 12 AND 17 THEN '12:00:00'
  ELSE '18:00:00'
END AS date_only, COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;