まえがき
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関数と何が違うのかあまりわかりませんでしたが、
フォーマットできるだけで色々できて非常に便利だな～と思いました。