data/timestamp型関数の仕様変更二題

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の16日目のエントリです。
今日は軽めのネタで。

make_date()

PostgreSQLにはmake_date()というSQL関数がある。

postgres=# \df make_date 
List of functions
-[ RECORD 1 ]-------+-----------------------------------------
Schema              | pg_catalog
Name                | make_date
Result data type    | date
Argument data types | year integer, month integer, day integer
Type                | normal

第1引数に整数で年、第2引数に整数で月、第3引数に整数で日を与えるとdate型を生成してくれるという関数だ。

PostgreSQL 9.6まで

PostgreSQL 9.6までは、年には正の整数しか指定できなかった。

postgres=# SELECT make_date(2017, 12, 16);
 make_date  
------------
 2017-12-16
(1 row)

年に負数や0を指定すると、範囲外というエラーになる。

postgres=# SELECT make_date(-1, 12, 16);
ERROR:  date field value out of range: -1-12-16
postgres=# SELECT make_date(0, 12, 16);
ERROR:  date field value out of range: 0-12-16

PostgreSQL 10から

「なんでmake_date()で紀元前の年が生成できねえんじゃ、ゴルァ！」と古代史に関するデータベースを作ろうとしてエラーになったから改造した・・・かどうかは不明だが、PostgreSQL 10からはmake_date()に、年に負の整数値を指定可能になった。負の整数値を与えた場合には(デフォルトのロケールだと)"BC"表記がつくようだ。

postgres=# SELECT make_date(-1, 12, 16);
   make_date   
---------------
 0001-12-16 BC
(1 row)

ただし、0については、PostgreSQL 10でもエラーになる。西暦0年は存在しないからだ。

postgres=# SELECT make_date(0, 12, 16);
ERROR:  date field value out of range: 0-12-16

おまけ

make_date()に与える年の正整数の上限っていくつなのか？というと、それはdate型の上限によって制限されている。date型は5,874,897年までは入力可能っぽい。太陽系が滅亡するまでの数十億年というスケールの年入力には使えないけど、数百万年程度の期間であれば使用には十分だろう。

to_timestamp()とto_date()

これらの関数は文字どおり、テキストで表現されたタイムスタンプや日時をtimestamp型やdate型に変更してくれるものだ。

PostgreSQL 9.6まで

しかし、この関数、意外とチェックがぬるかったりしていた。
例えば、12月32日なんて記述を許容してしまう。

postgres=# SELECT to_date('2017-12-32','YYYY-MM-DD');
  to_date   
------------
 2018-01-01
(1 row)

postgres=# SELECT to_timestamp('2017-12-32 00:00:00', 'YYYY-MM-DD');
      to_timestamp      
------------------------
 2018-01-01 00:00:00+09
(1 row)

誤った閏日を指定しても、3/1にマッピングしてしまう。

postgres=# SELECT to_date('2018-02-29','YYYY-MM-DD');
  to_date   
------------
 2018-03-01
(1 row)

PostgreSQL 10から

to_date(), to_timestamp()の日付の範囲チェックを厳密にするようになった。

postgres=# SELECT to_date('2017-12-32','YYYY-MM-DD');
ERROR:  date/time field value out of range: "2017-12-32"
postgres=# SELECT to_timestamp('2017-12-32 00:00:00', 'YYYY-MM-DD');
ERROR:  date/time field value out of range: "2017-12-32 00:00:00"

閏日もきちんとチェックするようだ。

postgres=# SELECT to_date('2018-02-29','YYYY-MM-DD');
ERROR:  date/time field value out of range: "2018-02-29"
postgres=# SELECT to_date('2020-02-29','YYYY-MM-DD');
  to_date   
------------
 2020-02-29
(1 row)

おわりに

• make_date()で紀元前の年入力が可能になった。
• to_date()とto_timestamp()で暦上、誤った指定を厳密にチェックするようになった。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.6. Functions

• Allow make_date() to interpret negative years as BC years (Álvaro Herrera)
• Make to_timestamp() and to_date() reject out-of-range input fields (Artur Zakirov)