PIB - SQLite - TIMESTAMP

2019-01-13: timezone

以下のような状況

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.4 LTS
Release:	16.04
Codename:	xenial

sqlite> .version
SQLite 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f

sqlite> SELECT datetime('2019-01-13 05:00:00');
2019-01-13 05:00:00
sqlite> SELECT datetime('2019-01-13 05:00:00+09:00');
2019-01-12 20:00:00
sqlite> SELECT datetime('2019-01-13 05:00:00 +09:00');
2019-01-12 20:00:00
sqlite> SELECT datetime('2019-01-13 05:00:00+0900');

sqlite> SELECT datetime('2019-01-13 05:00:00 +0900');

sqlite> SELECT datetime('2019-01-13 05:00:00JST');

sqlite> SELECT datetime('2019-01-13 05:00:00 JST');


sqlite> SELECT strftime('%F %T', CURRENT_TIMESTAMP);

sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP);
2019-01-12 20:10:15
sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S %z', CURRENT_TIMESTAMP);

sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S %Z', CURRENT_TIMESTAMP);


sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP, 'localtime');
2019-01-13 06:11:00
sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP, 'JST');

sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP, '+09:00');
2019-01-13 06:11:11
sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP, '+0900');


$ date --version
date (GNU coreutils) 8.25
Copyright (C) 2016 Free Software Foundation, Inc.
ライセンス GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

作者 David MacKenzie。
$ date -d "2019-01-13 05:00:00 +09:00" "+%F %T %z"
2019-01-13 05:00:00 +0900
$ date -d "2019-01-13 05:00:00 +09:00" "+%F %T %Z"
2019-01-13 05:00:00 JST

$ ruby -v
ruby 2.3.1p112 (2016-04-26) [x86_64-linux-gnu]
$ ruby -e 'p Time.now.strftime("%F %T")'
"2019-01-13 05:23:20"
$ ruby -e 'p Time.now.strftime("%F %T %z")'
"2019-01-13 05:23:25 +0900"
$ ruby -e 'p Time.now.strftime("%F %T %Z")'
"2019-01-13 05:23:27 JST"

SQLite の TIMESTAMP は文字列扱いという話らしいのだが、
基本的に UTC として扱い、辛うじて ±hh:mm の形式固定の timezone を parse 可能な模様ではあるようなのだが、
しかし strftime が %z, %Z を解さない。
一方、strftime(3) の %z は ±hhmm の形式なので、これに従う date や ruby 等とは互換性がない。
strftime の標準に従わないとか SQLite はアホなのか？
と思ったが、PostgreSQL はそもそも strftime がないし、変換関数に OF が導入されたのが 9.4、THH、THM が導入されたのが 11 となってる上、フォーマット指定なしで任意のタイムスタンプに関して自動的にパースしてくれる関数も存在しないらしく、こっちはこっちで地味に辛い状況だった orz

とりあえず、SQLite から外に出す時は
sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S +0000', CURRENT_TIMESTAMP); 
2019-01-13 21:12:22 +0000

sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S +0900', CURRENT_TIMESTAMP, '+09:00'); 
2019-01-13 06:12:22 +0900
外から SQLite に入れる時は
$ date "+%F %T %z"|sed -r 's/([+][0-9]{2})([0-9]{2})/\1:\2/g'
2019-01-13 06:14:37 +09:00

$ ruby -e 'p Time.now.strftime("%F %T %z").gsub(/([+][0-9]{2})([0-9]{2})/,"\\1:\\2")'
"2019-01-13 06:16:04 +09:00"
の様にしておくのが無難だろうか？

関連