MySQLで文字列を日にちに変換したい

Question

次のようなINSERT文で作ったテーブルがあります

CREATE TABLE login_log
(user_id  INTEGER,
 timestamp  VARCHAR(13));
 
INSERT INTO login_log VALUES (10001, '3/19/16 15:50'),
                        (10001, '3/20/16 0:20'),
                        (10001, '3/23/16 14:10'),
                        (10001, '3/24/16 0:15'),
                        (10002, '3/21/16 12:50'),
                        (10003, '3/23/16 11:00'),
                        (10003, '3/24/16 9:10'),
                        (10003, '3/24/16 11:20');

このtimestamp列を日にちに変換して日毎に集計しようとしましたが。次のコードでうまく行きません。多分DATA_FORMATの部分がおかしいと思うのですが、何が間違っているのでしょうか？ご教授お願いします

SELECT DATE_FORMAT(timestamp,'%m-%d-%Y') AS day,
COUNT(*) 
FROM login_log
GROUP BY day;

結果
day COUNT(*)
(null) 8

追記
このデータベースは元からあるもので、このテーブルの内容は変更できないとして、varchar (またはCHAR型)をdata型に変換して日毎に集計することが目的です

Answer 1

timestamp VARCHAR(13));

日付をvarchar で定義していることが間違いです。
datetime 型、timestamp型で定義すべきです。

SELECT DATE_FORMAT(STR_TO_DATE(`timestamp`, '%c/%e/%y %H:%i'), '%d-%m-%Y') FROM login_log

Comments

[trafalbad]

もともと文字列型のものをdatetime型に変更したいんです。ようはこのデータベースは元からあるもので、このテーブルの内容は変更できないとして、varchar (またはCHAR型)をdata型に変換して日毎に集計したい場合どうすればいいのでしょうか？

[退会済みユーザー]

STR_TO_DATE 関数を使って変換ですね。

[trafalbad]

解決しました、ありがとうございます！

Answer 2

こんにちは。

以下でいかがでしょうか？

SELECT COUNT(*), LEFT(timestamp, 4) AS day 
FROM login_log 
GROUP BY day

上記を実行すると、以下のようになりました。(phpMyAdminの画面キャプチャです）

参考になれば幸いです。

Comments

[退会済みユーザー]

10月〜12月に対応できないですよ。

[jun68ykt]

質問には、
====================================
次のようなINSERT文で作ったテーブルがあります

CREATE TABLE login_log
(user_id INTEGER,
timestamp VARCHAR(13));

INSERT INTO login_log VALUES (10001, '3/19/16 15:50'),
(10001, '3/20/16 0:20'),
(10001, '3/23/16 14:10'),
(10001, '3/24/16 0:15'),
(10002, '3/21/16 12:50'),
(10003, '3/23/16 11:00'),
(10003, '3/24/16 9:10'),
(10003, '3/24/16 11:20');
====================================
と書いてあるので、

> 10月〜12月に対応

というのは与件の範囲外という認識でした。

[trafalbad]

回答ありがとうございました