まえがき

MySQL-DATE_FORMAT関数の挙動確認です。

前提条件

MySQLで簡単にランダムなテストデータを作成する方法
こちらの記事にランダムデータを作成する方法が載っていましたので、同様の手順でテーブルを作成しておきます。

20210801024646_ChangeUsers.php

# id, name, description, price, created_at
1, 商品1, 8a44ac7a37f7bd69a7db99d3fe937b, 8595, 2014-04-18 09:12:02
2, 商品2, 493b3579d0986774aed5dab8dd8ca4, 5489, 2014-02-28 23:28:33
3, 商品3, a022f42410b9bd6ec080c3e5112be3, 6452, 2014-03-28 10:54:58
4, 商品4, 2be6c9822811b7586c46e86417e466, 8356, 2014-04-29 18:44:42
6, 商品6, cf2151b45afa9c148f2260df480617, 3202, 2014-05-25 01:16:46
7, 商品7, 452fcde3e7130a92e51015240d8af2, 1319, 2014-01-16 00:35:53

こんな風にあっという間に数十万レコードのダミーデータが作成されます。

DATE_FORMAT関数

DATE_FORMAT 関数を使うと引数に指定した日付を表す値を指定のフォーマットで整形した文字列を取得します。また TIME_FORMAT 関数を使うと時刻を表す値を指定のフォーマットで整形した文字列を取得できます。
DATE_FORMAT関数 / TIME_FORMAT関数 (日付または時刻を指定したフォーマットで整形する)

PHPでいうところのdate関数やstrtotime関数のような事が可能という事ですね。

・日時情報の変換

日付 - スラッシュ区切り

select created_at as bef,date_format(created_at, '%Y/%m/%d') as aft  from item

# bef, aft
'2014-04-18 09:12:02', '2014/04/18'
'2014-02-28 23:28:33', '2014/02/28'
'2014-03-28 10:54:58', '2014/03/28'
'2014-04-29 18:44:42', '2014/04/29'
'2014-05-25 01:16:46', '2014/05/25'

日付 - 英語月名 / 英字曜日 / AM・PM表記

select created_at as bef,date_format(created_at, '%Y/%m/%d') as aft  from item

# bef, aft
'2014-04-18 09:12:02', 'Apr-18(Friday) AM 09'
'2014-02-28 23:28:33', 'Feb-28(Friday) PM 11'
'2014-03-28 10:54:58', 'Mar-28(Friday) AM 10'
'2014-04-29 18:44:42', 'Apr-29(Tuesday) PM 06'
'2014-05-25 01:16:46', 'May-25(Sunday) AM 01'

こんな事も可能です。

SELECT文でgroup関数と併用する事で指定フォーマットの日付による絞り込みを行う事ができます。

created_atカラムの値を日付までの値でフォーマットし、
日付が等しいレコード数を集計する。

select count(*) as C, date_format(created_at, "%Y-%m-%d") as D from item group by D order by  C DESC limit 10;

# C, D
'8', '2014-04-24'
'8', '2014-01-25'
'7', '2014-05-25'
'7', '2014-01-29'
'7', '2014-02-16'
'7', '2014-05-17'
'6', '2014-03-12'
'6', '2014-06-14'
'6', '2014-03-13'
'6', '2014-05-07'

created_atカラムの値を月までの値でフォーマットし、
月内のレコード数を集計、priceカラムの合計値を返す。

select CONCAT(CAST(SUM(price) as char(20)),' yen') as P,count(*) as C, date_format(created_at, "%Y-%m") as D from item group by D order by  P DESC;

# P, C, D
'491773 yen', '94', '2014-06'
'479018 yen', '97', '2014-01'
'453588 yen', '82', '2014-04'
'424187 yen', '79', '2014-03'
'418671 yen', '86', '2014-05'
'368871 yen', '74', '2014-02'

あとがき

調べている時はgroup関数と何が違うのかあまりわかりませんでしたが、
フォーマットできるだけで色々できて非常に便利だな～と思いました。