SQLでUNIX時刻をyyyy-mm-dd h:m:s書式(JST)に変換する方法

うーんUNIX時刻か

先日、あるデータで

Select Time1 from mydata limit 1;

としたところ、

1516031280

と表示されました。 Timeと言えば、日常生活で普通に目にする yyyy-mm-dd hh:mm:ss　(2018-02-01 12:34)のような形を期待していましたが、どうやらUNIX時刻(DDDDDDDDDD 書式)らしいです。

UNIX時刻をSQLでyyyy-mm-dd 等にする

UNIX時刻とは

WiKipediaより　

UNIX時間（ユニックスじかん）またはUNIX時刻（ユニックスじこく、UNIX time（ユニックスタイム）、POSIX time（ポジックスタイム））とはコンピューターシステム上での時刻表現の一種。UNIXエポック、すなわち協定世界時 (UTC) での1970年1月1日午前0時0分0秒から形式的な経過秒数（すなわち、実質的な経過秒数から、その間に挿入された閏秒を引き、削除された閏秒を加えたもの）として表される。

　なんでUNIX時刻なんだろうと考えましたが、

• 閏秒の扱いを除けば、ほぼUTCなのでタイムゾーンによる影響を受けません。
• 整数表現なので差分計算など取り回しが容易
• 秒単位なので実用的にはほぼ事足りる くらいしか思いつきません。
  とにかくこれをyyyy-mm-dd h:m:s書式(JST)にしないとわかりづらくて困ります。

SQLiteではdatetime関数でUNIX時刻を簡単にyyyy-mm-dd h:m:s書式(JST)に変換できる

　SQLite Query Language: Date And Time Functionsを参考に試してみます。

sqlite>Select Time1 from mydata limit 1;
1516031280

sqlite>select datetime(time,'unixepoch') as Time from mydata limit 1;
Time
2018/1/15 3:48:00 PM

sqlite>select datetime(time,'unixepoch','localtime') as Time from mydata limit 1;
Time
2018/1/16 12:48:00 AM

　SQLiteの datetime() 関数は strftime() の　wrapper　です。第1引数は処理対象となる日付時刻ですが、第2引数以降に指定できるパラメータが多数存在します。その分、設定次第使い方次第とも言えます。
　第1引数にUNIX時刻(DDDDDDDDDD書式)を与えたときのみ'unixepoch'パラメータが有効になり、第1引数をUNIX時刻と認識します。このままだとUTC時刻を返し、'localtime'を併用すると現地の時刻となります。我々ですとJST(日本標準時)です。

MySQLの場合

　MySQLでもUNIX時刻をyyyy-mm-dd 等に変換することができます。

>select Time1 from mydata limit 1;
Time1
1516031280

>select from_unixtime(Time1) from mydata limit 1;
Time1
2018/1/16 12:48:00 AM

　MySQLの from_unixtime() を実行すると、localtimeで結果を返すらしく、当環境ではJSTで結果が表されています。このあたりの挙動はしっかり把握しておかないとトラブルになりかねません。