毎回忘れる、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 | +---------------------+
と、このような形になります。タイムゾーンだけ、どうやって変換するのかわからなかったです（ごめんなさい）
随時、例は追加していきます
