⏰

SQL ServerでFORMAT関数を使って期間ごとのデータを集計する

に公開
SQL
SQL Server
Microsoft
tech

SQL ServerでFORMAT関数を使って日間・時間・分間・秒間ごとのデータを抽出します。

CONVERTを使う方法や、DATEDIFFとDATEADDを駆使する方法などもありますが、今回はFORMATを使う方法を紹介します。

日間

SELECT
	COUNT(*) AS CNT
	,FORMAT(timestamp, 'yyyy-MM-dd') AS '日間'
FROM sample_table
WHERE timestamp BETWEEN @StartDateTime AND @EndDateTime
GROUP BY FORMAT(timestamp, 'yyyy-MM-dd')
ORDER BY FORMAT(timestamp, 'yyyy-MM-dd')

抽出例

CNT 日間
10435 2021-01-01
12587 2021-01-02
9938 2021-01-03

時間

SELECT
	COUNT(*) AS CNT
	,FORMAT(timestamp, 'yyyy-MM-dd HH') AS '時間'
FROM sample_table
WHERE timestamp BETWEEN @StartDateTime AND @EndDateTime
GROUP BY FORMAT(timestamp, 'yyyy-MM-dd HH')
ORDER BY FORMAT(timestamp, 'yyyy-MM-dd HH')

抽出例

CNT 時間
1305 2021-03-01 01
1023 2021-03-01 02
1189 2021-03-01 03

分間

SELECT
	COUNT(*) AS CNT
	,FORMAT(timestamp, 'yyyy-MM-dd HH:mm') AS '分間'
FROM sample_table
WHERE timestamp BETWEEN @StartDateTime AND @EndDateTime
GROUP BY FORMAT(timestamp, 'yyyy-MM-dd HH:mm')
ORDER BY FORMAT(timestamp, 'yyyy-MM-dd HH:mm')

抽出例

CNT 分間
129 2021-03-01 01:00
105 2021-03-01 01:01
97 2021-03-01 01:02

秒間

SELECT
	COUNT(*) AS CNT
	,FORMAT(timestamp, 'yyyy-MM-dd HH:mm:ss') AS '秒間'
FROM sample_table
WHERE timestamp BETWEEN @StartDateTime AND @EndDateTime
GROUP BY FORMAT(timestamp, 'yyyy-MM-dd HH:mm:ss')
ORDER BY FORMAT(timestamp, 'yyyy-MM-dd HH:mm:ss')

抽出例

CNT 秒間
11 2021-03-01 01:00:00
16 2021-03-01 01:00:01
18 2021-03-01 01:00:02

参考

Microsoft - FORMAT (Transact-SQL)

Discussion