PostgreSQLでISO8601形式の日付を扱う（タイムゾーン付き）

PostgreSQLでタイムスタンプの文字列から、インポートするときにちょっと困ったのでメモ。

例えばC#でいうところの

Console.WriteLine(DateTimeOffset.Now.ToString("o"));
//2018-02-17T23:50:31.5652713+09:00

こういうISO8601形式のタイムスタンプをパースしたい。PostgreSQLではこうやるらしい。

postgres=# SELECT to_timestamp('2018-02-17T23:50:31.5652713+09:00', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
      to_timestamp
------------------------
 2018-02-17 23:50:31+09
(1 row)

これはタイムゾーンありのtimestamptz型になるので、タイムゾーンを落としたければtimestamp型でキャストすればよい。

postgres=# SELECT to_timestamp('2018-02-17T23:50:31.5652713+09:00', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')::timestamp;
    to_timestamp
---------------------
 2018-02-17 23:50:31
(1 row)

逆に文字列にする場合、いちいちto_charするのではなく、PostgreSQL9.4以降ではto_json()をかませるのがスマートらしい。StackOverFlowに載っていた。

postgres=# SELECT to_json(now());
              to_json
------------------------------------
 "2018-02-18T00:04:14.328248+09:00"
(1 row)

なるほど～～～これはイケメンやな～～と思った。

stackoverflow.com