MySQLでの文字列の日付から日付形式に変換する方法（STR_TO_DATE)

毎回忘れる、MYSQLの文字列->日付変換についてメモしました。例えば、rssのフィード内によくある、publishedとかとか。。これを日付/時刻型に変換するにはどうすればいいか？なんていうのを例につらずらと書いていきます。

<pubDate>Sun, 29 Jul 2018 21:00:00 +0900</pubDate>

STR_TO_DATE

STR_TO_DATE() は、書式文字列に日付と時間の両方の部分が含まれる場合は DATETIME 値を返し、文字列に日付と時間の部分の一方のみが含まれる場合は DATE または TIME 値を返します。DATE_FORMAT関数とは逆の動きをするといった感じでしょうか。

書式

STR_TO_DATEの書式は以下の通りです

STR_TO_DATE( 文字列 , フォーマット)

誤った 文字列 が含まれている場合、STR_TO_DATE() は NULL になります

フォーマットについて

フォーマット	説明
%a	週の名前（省略） (Sun - Sat)
%b	月の名前（省略）(Jan - Dec)
%c	月 (0 - 12)
%D	月の名前 (1st, 2nd, 3rd, ...)
%d	日 (01 - 31)
%e	日 (0 - 31)
%f	マイクロ秒 (000000 - 999999)
%H	時(24時間表記） (00 - 23)
%h	時(12時間表記） (00 - 12)
%I	時(12時間表記） (00 - 12)
%i	分 (00 - 59)
%j	一年換算での日数 (001 - 366)
%k	時（頭ゼロなし） (0 - 23)
%l	時（12時間表記）(1 - 12)
%M	月の名前（完全） (January - December)
%m	月 (00 - 12)
%p	午前/午後（AM or PM）
%r	AM/PM時間形式 (hh:mm:ss AM/PM)
%S	秒 (00 - 59)
%s	秒 (00 - 59)
%T	24時間フォーマット (hh:mm:ss)
%U	わからん
%u	わからん
%V	わからん
%v	わからん
%W	週の名前（完全） (Sunday - Saturday)
%w	週の番号 (Sunday=0 -> Saturday=6)
%X	わからん
%x	わからん
%Y	年(2018)
%y	年(18)

まあ、わからんものは使い方がよくわからないので説明を割愛しました（笑）

例

お題目含めて、例を書いていきます。

お題目のrss feedのpublished

Tue, 03 Jul 2018 10:49:00 +0900

SELECT STR_TO_DATE("Tue, 03 Jul 2018 10:49:00 +0900","%a, %d %b %Y %H:%i:%s") + INTERVAL 9 HOUR AS str_date;
+---------------------+
| str_date            |
+---------------------+
| 2018-07-03 19:49:00 |
+---------------------+

もしくは、

SELECT CONVERT_TZ(STR_TO_DATE("Tue, 03 Jul 2018 10:49:00 +0900","%a, %d %b %Y %H:%i:%s"),'+00:00','+09:00') AS str_date;
+---------------------+
| str_date            |
+---------------------+
| 2018-07-03 19:49:00 |
+---------------------+

と、このような形になります。タイムゾーンだけ、どうやって変換するのかわからなかったです（ごめんなさい）

随時、例は追加していきます