TEXT型カラムの値をINTERVAL型に変換し、その値を別のINTERVAL型カラムに代入する方法


環境: PostgreSQL9.4

現在、0秒〜2日までの時間データを保持するのに、TEXT型カラムdurationへ次のような文字列を保存するようにしています。

  * PT10S
  * PT1H10M20S
  * P1D

このフォーマットはISO8601に準拠している為、例えば次のようにEXTRACT()を使い変換できます。

[code]

    postgres=# SELECT EXTRACT(EPOCH FROM INTERVAL 'PT1M10S');
    date_part
    -----------
            70
    (1 row)
    
[/code]

これをソートする必要が出てきたのですが、文字列のままでは正しくソートされないので、これを次の手順でINTERVAL型に変えることにしました。

  1. INTERVAL型の新しいカラムduration_tmpを作成
  2. 全レコードのdurationの値をEXTRACT()で変換し、その値をduration_tmpに代入
  3. durationを削除し、duration_tmpの名前をdurationに変更

この2. の方法が分からず困っています。一度次のようなコマンドを打ったのですがうまくいきません。

[code]

    postgres=# UPDATE my_table SET duration_tmp = EXTRACT(EPOCH FROM INTERVAL duration);
    ERROR:  syntax error at or near "duration"
    LINE 1: ...le set duration_tmp = EXTRACT(EPOCH FROM INTERVAL duration);
                                                                 ^
    
[/code]

どのようにすれば良いでしょうか？




CAST してしまえば良いのではないでしょうか。

[code]

    postgres=# SELECT CAST('PT1M10S' as INTERVAL);
    interval 
    ----------
    00:01:10
    (1 row)
    
[/code]

ですので、

[code]

    SELECT duration FROM hoge_table ORDER BY CAST(duration as INTERVAL);
    
[/code]

となるかと思います。実環境がないので間違っているかもしれません。

~~ところで、例として挙げられた ISO8601 形式の時間データのうち、`PT1D` だけ書式エラーとなりました。

[code]

    postgres=# SELECT CAST('PT1D' as INTERVAL);
    ERROR:  invalid input syntax for type interval: "PT1D"
    
[/code]

~~




CAST してしまえば良いのではないでしょうか。

[code]

    postgres=# SELECT CAST('PT1M10S' as INTERVAL);
    interval 
    ----------
    00:01:10
    (1 row)
    
[/code]

ですので、

[code]

    SELECT duration FROM hoge_table ORDER BY CAST(duration as INTERVAL);
    
[/code]

となるかと思います。実環境がないので間違っているかもしれません。

~~ところで、例として挙げられた ISO8601 形式の時間データのうち、`PT1D` だけ書式エラーとなりました。

[code]

    postgres=# SELECT CAST('PT1D' as INTERVAL);
    ERROR:  invalid input syntax for type interval: "PT1D"
    
[/code]

~~

