trdsql 日付・時刻処理

Posted on:

ファイル内に入っている日付、時刻をそのまま扱う場合は良いですが、変換等の処理をしたい場合があります。

その場合は、一旦日付や時刻と解釈させてから扱う方が扱いやすくなります。

SQLite3の日付、時刻処理

デフォルトのSQLite3の日付、時刻処理では、以下のフォーマットであれば、日付、時刻として解釈することができます。 もしSQLite3のエンジンで処理したい場合は、このフォーマットにしておくと良いでしょう。

  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD

以下のようなログファイルのtimeを処理したい場合、

time:2015-09-06T05:58:05+09:00	method:POST	...
time:2015-09-06T05:58:41+09:00	method:POST	...
time:2015-09-06T06:00:42+09:00	method:GET	...

datetime(time)で日時として、認識させれば、strftime()で再フォーマットがしやすくなります。

trdsql -iltsv "SELECT strftime('%Y年%m月%d日%H時%M分%S秒',datetime(time)) FROM log.ltsv"

2015年09月05日20時58分05秒
2015年09月05日20時58分41秒
2015年09月05日21時00分42秒

上記以外のフォーマットの場合は、SQLite3では文字列をまず書き換える必要があります。

PostgreSQLの日付、時刻処理

PostgreSQLの日付、時刻処理は、より豊富なフォーマットを処理できます。

多くの場合は、dateやtimestampにCASTするだけで、多くの有名なフォーマットは解釈されます。

trdsql -driver postgres -dsn "dbname=trdsql_test" "SELECT to_char(CAST(time AS timestamp),'YYYY年MM月dd日HH24時MI分ss秒') FROM log.ltsv"

2015年09月06日05時58分05秒
2015年09月06日05時58分41秒
2015年09月06日06時00分42秒

日付、時刻型に変換されるので、そこから表示するフォーマットに変換するにはto_char()を使用します。指定の仕方はマニュアルを参照して下さい。

さらに独特なフォーマットの場合は、 to_dateやto_timestampにより自分で定義したフォーマットで解釈させることが出来ます。

例えば上記で出力したフォーマットの場合、to_charと同じフォーマット指定でto_timestampを実行すれば逆にタイムスタンプとして扱われます。

trdsql -ih -oh  -driver postgres -dsn "dbname=trdsql_test" "SELECT to_timestamp(\"日時\",'YYYY年MM月dd日HH24時MI分ss秒') FROM d.csv"

2015-09-05T20:58:05+09:00
2015-09-05T20:58:41+09:00
2015-09-05T21:00:42+09:00

MySQLの日付、時刻処理

MySQLでも多くのフォーマットをdate()やtimestamp()により変換させることができます。

trdsql -driver mysql -dsn "noborus:noborus@/trdsql_test" -oat "SELECT date(time),timestamp(time) FROM log.ltsv"

+------------+----------------------------+
| date(time) |      timestamp(time)       |
+------------+----------------------------+
| 2015-09-06 | 2015-09-06 05:58:05.000000 |
| 2015-09-06 | 2015-09-06 05:58:41.000000 |
| 2015-09-06 | 2015-09-06 06:00:42.000000 |
+------------+----------------------------+

独自のフォーマットを解釈させる場合は、STR_TO_DATE()を使用します。 上記の年月日時分秒を解釈させるには次のようにします。

trdsql -ih -driver mysql -dsn "noborus:noborus@/trdsql_test" "SELECT STR_TO_DATE(\`日時\`,'%Y年%m月%d日%H時%i分%s秒') FROM d.csv"

2015-09-05 20:58:05
2015-09-05 20:58:41
2015-09-05 21:00:42

日時から表示するフォーマットには、DATE_FORMAT()が使用できます。「/」で日付を表示してみます。

trdsql -ih -driver mysql -dsn "noborus:noborus@/trdsql_test" "SELECT DATE_FORMAT(STR_TO_DATE(\`日時\`,'%Y年%m月%d日%H時%i分%s秒'),'%Y/%m/%d') FROM d.csv"

2015/09/05
2015/09/05
2015/09/05

日付、時刻処理はフォーマットがそれぞれ違うので、各データベースのマニュアルを参照して下さい。