Mac De Oracle Heterogeneous! #19 Tweet
続きです。
日付、時間型がOracle側にはどのようにマッピングされるのか、どのような注意点があるのか検証してみる。
今日はPostgreSQL7.4.9の日付型、時間型について確認してみることにする
まず、PostgreSQL7.4.9のdate型、timestamp型、time型が、Oracleのdate型やtimestamp型とどのような違いがあるのかPostgreSQL上で確認してみることにする。
Oracleだとdate型とtimestamp型だけでなのでdate型が年月日だけ、time型が時間だけを格納するという点は覚えておくと良さそう。
timestamp型はOracleのtimestamp型と同じようなので特に注意する点はなさそうである。
重要なのは、Oracleのdate型やtimestamp型は日付や時間の妥当性チェックが厳しいというか、しっかりしているので日付として妥当でない文字列を受け入れることはないのだが、PostgreSQL7.4.9では、それを”調整して”受け入れてしまう点である。PostgreSQLに慣れている方なら驚くことではないと思うが、Oracleには慣れているが、PostgreSQLは初心者ということになると、かなり戸惑うことになる。(前回利用したdate_test表にtime型の列を追加した表を利用した。)
Last login: Fri Jan 20 22:41:46 on ttyp1
Welcome to Darwin!
pb17:˜ stargrass$ su - postgres
Password:
pb17:˜ postgres$ postmaster -S
pb17:˜ postgres$
pb17:˜ postgres$ psql -U scott postgresql749
Password:
Welcome to psql 7.4.9, the PostgreSQL interactive terminal.
Type: ¥copyright for distribution terms
¥h for help with SQL commands
¥? for help on internal slash commands
¥g or terminate with semicolon to execute query
¥q to quit
postgresql749=> alter table date_test add column r_time time;
ALTER TABLE
postgresql749=> commit;
COMMIT
postgresql749=> ¥d date_test;
Table "scott.date_test"
Column | Type | Modifiers
-------------+-----------------------------+-----------
r_date | date |
r_timestamp | timestamp without time zone |
r_time | time without time zone |
postgresql749=> select * from date_test;
r_date | r_timestamp | r_time
------------+---------------------+--------
1900-01-01 | |
2006-01-20 | |
| 1981-01-01 00:00:00 |
| 1990-12-01 00:00:00 |
(4 rows)
※Oracleならエラーになる。
postgresql749=> insert into date_test values(to_date('00000000','yyyymmdd'),null,null);
INSERT 25433 1
※Oracleならエラーになる。
postgresql749=> insert into date_test values(to_date('20061435','yyyymmdd'),null,null);
INSERT 25434 1
※Oracleならエラーになる。
postgresql749=> insert into date_test values(null,to_date('00000000','yyyymmdd'),null);
INSERT 25435 1
※Oracleならエラーになる。
postgresql749=> insert into date_test values(null,to_date('20062040','yyyymmdd'),null);
INSERT 25436 1
※Oracleならエラーになる。
postgresql749=> insert into date_test values(null,to_date('20060101479999','yyyymmddhh24miss'),null);
INSERT 25437 1
postgresql749=> insert into date_test values(null,null,'235959');
INSERT 25438 1
※Oracleならエラーになる。
postgresql749=> insert into date_test values(null,'2006-01-01 240000',null);
INSERT 25439 1
※Oracleならエラーになる。
postgresql749=> insert into date_test values(null,'2006-01-01 266060',null);
INSERT 25440 1
postgresql749=> insert into date_test values(current_date, current_timestamp, current_time);
INSERT 25441 1
※Oracleならエラーになる。
postgresql749=> insert into date_test values(null,to_timestamp('20063433284963','yyyymmddhh24miss'),null);
INSERT 25442 1
postgresql749=> commit;
COMMIT
postgresql749=> select * from date_test;
r_date | r_timestamp | r_time
---------------+----------------------------+-----------------
1900-01-01 | |
2006-01-20 | |
| 1981-01-01 00:00:00 |
| 1990-12-01 00:00:00 |
0001-01-01 BC | |
2007-03-07 | |
| 0001-01-01 00:00:00 BC |
| 2007-09-11 00:00:00 |
| 2006-01-01 00:00:00 |
| | 23:59:59
| 2006-01-02 00:00:00 |
| 2006-01-02 03:01:00 |
2006-01-22 | 2006-01-22 21:57:25.372923 | 21:57:25.372923
| 2008-11-07 04:50:03 |
(14 rows)
postgresql749=>
postgresql749=>
※空文字は指定できないようだ。(Oracleでは、空文字もNULLとして扱っているので Genneric Connectivity経由だとエラーにはならないかもしれない)
postgresql749=> insert into date_test values('',null,null);
ERROR: invalid input syntax for type date: ""
postgresql749=> rollback;
ROLLBACK
見ての通り、Oracleではエラーになるケースが含まれているのだがPostgreSQLではすべて正常に登録できてしまう。(しかも日付は調整された形で登録されてしまう。)
Oracleに慣れていると日付や時間に設定する文字列が日付や時間として妥当であるか厳密にチェックされているという事に慣れすぎているのでかなり驚く。
14月とか20月、63秒なんて存在しないからエラーにするという考え方のOracleと、14月と指定すれば1年と2ヶ月として”勝手に”日付を調整してくれるPostgreSQL、戸惑わないほうがおかしい。
上記は、to_date()や、to_timestamp()という関数の動きなのだが、以下のようにto_date()やto_timestamp()関数を利用しないで暗黙型変換?を利用すると挙動が異なるのでさらに戸惑う。
文字列のままで暗黙型変換だと、ちゃんと文字列が日付や時間として妥当な文字列かチェックしてくれるのである。いや〜結構ハマりそうだ。
postgresql749=> insert into date_test values('00000000',null,null);
ERROR: date/time field value out of range: "00000000"
HINT: Perhaps you need a different "datestyle" setting.
postgresql749=> insert into date_test values('20060101',null,null);
ERROR: current transaction is aborted, commands ignored until end of transaction block
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into date_test values('20060101',null,null);
INSERT 25443 1
postgresql749=> insert into date_test values('20061349',null,null);
ERROR: date/time field value out of range: "20061349"
HINT: Perhaps you need a different "datestyle" setting.
postgresql749=> insert into date_test values('2006-13-49',null,null);
ERROR: current transaction is aborted, commands ignored until end of transaction block
postgresql749=> select * from date_test;
ERROR: current transaction is aborted, commands ignored until end of transaction block
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into date_test values('2006-13-49',null,null);
ERROR: date/time field value out of range: "2006-13-49"
HINT: Perhaps you need a different "datestyle" setting.
postgresql749=> rollback;
ROLLBACK
postgresql749=>
ということで今日はここまで。
次回は、Genneric Connectivity経由でPostgreSQLに日付や時間を登録してみる。(どのようになるか楽しみである。)
---
都心では9センチの積雪とか言っていたけど、車にはもっと積もっているような気がする。
| 固定リンク | 0
コメント