【MySQL】クエリの中でISO 8601形式の日時文字列をつくる

　この記事に書く方法よりもできることならばwebサーバー側のプログラムで適宜ISO 8601形式等の日時のフォーマット変換をした方が無難です。文字列結合や参照しないフィールドにおける値の加工などをすると無駄にプログラムが重くなりやすいです。単にそういうこともできるという小ネタ的な記事です。一応データベースとそれ以外でそれぞれマシンが独立していて極端にデータベース側のマシンが優秀であったりするならば有効やもしれませんが、それくらいです。

　ISO 8601形式の日付と時刻は国際標準として広く使用される形式です。私的に最もありがたい点はどのJavaScript実行環境でもこの形式なら Date オブジェクトに変換してくれる、変換できないならば環境側に問題がある、と確定している点です。これはMDNに説明があり、次の引用がそれです。

Date() コンストラクター – JavaScript ｜ MDN

メモ: Date コンストラクター（および Date.parse と同等）で日付文字列を解釈する際には、常に入力が ISO 8601 形式 (YYYY-MM-DDTHH:mm:ss.sssZ) であることを確認してください。他の形式で解釈した場合には、その挙動は実装によって定義されていて、すべてのブラウザーで動くとは限りません。 RFC 2822 書式の文字列の対応は慣習的に行われているだけです。多数の異なる形式に対応するためには、ライブラリーが役に立ちます。

日付のみの文字列（例: "1970-01-01"）は UTC として扱われ、日付時刻の文字列（例: "1970-01-01T12:00"）は ローカルとして扱われます。したがって、入力形式が 2 つの型の間で一貫していることを確認することも推奨されます。

　Dateとして解釈できる、できないの問題が現れやすいプログラムは異なるブラウザで様々な人が見るwebページのプログラムです。このためwebサーバーとクライアントのやりとりではとりあえずISO 8601形式にしておくのが無難です。

　この様に便利なISO 8601形式ですが MySQL ではこの形式を作れません。これは恐らくMySQLはただ一つのタイムゾーンを設定として持ち全ての日時はそのタイムゾーンの値であるとして扱っているためタイムゾーンが重要な形式であるISO 8601形式が不要なためです。もしデータごとのタイムゾーンを意識する必要があるのであれば、日時データとは別にタイムゾーンデータを保持する必要があります。

　その様なISO 8601形式が不得手なMySQLですが、ISO 8601形式の日時を返せないわけではありません。日時データから時分秒を抜き出す、文字列を操作する、タイムゾーンの変換を行う、などといった単体の機能はMySQLに揃っており、これを使えば十分ISO 8601形式の日時文字列を構築できます。

　構築する前の素材として大事なのがMySQLで設定されたタイムゾーンを+09:00の様な形式で得る方法です。これは例えば次でできます。

# まずタイムゾーンを時刻形式で得る方法です。

# @@session.time_zone には Asia/Tokyo が入っています
SELECT TIMEDIFF(NOW(), CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')) as offset;
# NOW()は現在のMySQLの設定のタイムゾーンで現在日時を返します。
# @@session.time_zoneは現在のMySQLの設定のタイムゾーンを返します。
# CONVERT_TZ はタイムゾーンの変換を行います。
# MuSQLの設定のタイムゾーンにおける現在日時とUTCにおける現在日時の時刻の差を計算します。
# 結果 09:00:00 が得られます。

# 次いで時刻形式のタイムゾーンを利用して +09:00 の様な形式にする方法です。
SELECT
    IF(
        TIMEDIFF(NOW(), CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')) = 0,
        'Z',
        CONCAT(
           IF(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')) >= 0, '+', '-'),
           TIME_FORMAT(
               TIMEDIFF(NOW(), CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')),
               '%H:%i'
           )
       )
    )as offset;
# IFでもしタイムゾーンによる時刻オフセットなしなら Z、そうでないなら符号のあるオフセット文字列構築の分岐をする
# IFで先ほど得た 09:00:00 の様な時刻オフセットが正か負かを調べて符号をつける
# TIME_FORMATで先ほど得った 09:00:00 の様な時刻オフセットを　09:00 の様な形式にする
# CONCATで結合してISO 8601形式のタイムゾーンの形にする

　このSQLを使うことで現在のデータベースの設定のタイムゾーン由来の時刻オフセットをISO 8601形式で表現できます。これを使って次の様なSQLで適宜目的の日時データを加工すればMySQLの中だけでISO 8601形式の日時文字列を構築できます。

SELECT
    created_at,
    # 日と時の間を空白からTに変換して、先ほどのオフセットを結合
    CONCAT(REPLACE(created_at, ' ', 'T'), offset) created_at_as_ISO 8601
FROM accounts
# CROSS JOINで一行一列の表の内容を accounts テーブルの各行に結合させた表を作る
CROSS JOIN (
    SELECT
        IF(
            TIMEDIFF(NOW(), CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')) = 0,
            'Z',
            CONCAT(
                IF(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')) >= 0, '+', '-'),
                TIME_FORMAT(
                    TIMEDIFF(NOW(), CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')),
                    '%H:%i'
                )
            )
        )as offset
) timezone_offset

　こうすることでMySQLの設定がどの様になっていてもISO 8601形式の日時文字列をMySQLの中だけで作れます。あえてこの方式を使う理由はそうそうないですが、一応こんなこともできるとかそんな話です。