《卸甲筆記》-PostgreSQL和Oracle的資料類型的對比系列三：時間類型

postgresql是世界上功能最強大的開源資料庫，在國内得到了越來越多機構和開發者的青睐和應用。随着postgresql的應用越來越廣泛，oracle向postgresql資料庫的資料遷移需求也越來越多。資料庫之間資料遷移的時候，首先遇到的，并且也是最重要的，就是資料類型之間的轉換。下面根據自己的了解和測試，寫了一些資料類型之間的差異以及遷移時的注意事項的文章，不足之處，尚請多多指教。

oracle日期時間類型有兩類，一類是日期時間類型，包括date， timestamp with time zone， timestamp with local time zone。另一類是interval類型。主要有interval year to month 和interval day to second兩種。postgresql也有類似的兩類。其中的日期時間類型包括timestamp with time zone， timestamp without time zone， date，time with time zone , time without time zone五種。interval類型就是interval。它們之間的對應關系是什麼呢?

oracle的date類型包括年、月、日、時、分、秒六個字段， 時間跨度是公元前4712年1月1日~公元9999年12月31日。postgresql中，可以使用timestamp(0) without time zone來對應，時間跨度從公元前4713年~公元294276年。

所有oracle的日期時間類型，時間跨度都是公元前4712年1月1日~公元9999年12月31日。而postgresql的時間跨度遠遠超過oracle的時間跨度。postgresql中timestamp的時間跨度遠大于oracle的，而date類型的時間跨度比timestamp的更大。是以在時間的極值方面不存在問題。

注意：postgresql中的date類型僅僅包含日期，不包括時、分、秒資訊。是以不能使用date類型來對應oracle的date類型。

oracle date

postgresql timestamp(0) without time zone

oracle的timestamp包含年、月、日、時、分、秒、毫秒。其中最後的毫秒的精度最大為9位，預設為6位。基本上等同于postgresql的timestamp without time zone。後者同樣包含年、月、日、時、分、秒、毫秒。最後的毫秒精度最大為6位，預設為6位。精度方面，postgresql比oracle稍差。但是實際應用中，毫秒的6位精度已經足夠。

oracle資料中的毫秒數如果小于精度，會在有效資料後自動以0補足位數。postgresql的毫秒數如果小于精度，不會在末尾補0。

oracle timestamp 預設精度

postgresql timestamp without time zone 預設精度

oracle timestamp 指定精度

postgresql timestamp without time zone 指定精度

oracle的timestamp(p) with time zone類型，是在資料庫的字段裡面儲存本字段相關的時區資訊。功能方面基本上等同于postgresql的timestamp(p) with time zone。不同之處在于：oracle會在資料庫字段中存儲資料所在的時區，而postgresql卻是把資料的時區自動轉換成為資料庫的時區(初始值由postgresql.conf中的timezone定義，可以使用set timezone來進行更改)。

資料精度方面，和 1.2、timestamp(p)類型 相同。

oracle timestamp with time zone

postgresql timestamp(p) with time zone

oracle的timestamp(p) with local time zone類型，是在資料庫的字段中不儲存本字段的時區資訊。而是使用資料庫的time zone資訊。當将資料寫入資料庫的時候，會自動将資料轉換為資料庫的時區寫入。當取得資料的時候，會将資料轉化為使用者session所在的時區進行顯示。這種資料類型也對應于postgresql的timestamp with time zone。

oracle tiimestamp with local time zone

oracle的interval 類型表示時間的間隔。分為interval year(p) to month 和 interval day(p) to second兩類。分别對應間隔為年月和 間隔為日、時、分、秒的情況。postgresql的資料類型中，也有interval 資料類型。

oracle的interval year(p) to month類型表示年月的時間間隔。year, month一起使用的時候，寫法類似于interval '12-11' year to month。其中的年份根據精度p來決定，p值為1~4，預設為2。月份隻能是0~11之間的值。其實year, month都可以單獨使用。單獨使用時，年份同樣是根據精度p來決定。postgresql的interval中，有類似的用法。而且用法比oracle還靈活。

oracle interval year(p) to month

postgresql interval

oracle的interval day(p) to second類型表示日、時、分、秒的時間間隔。一起使用的時候，寫法類似于interval '12 5:6:7.8' day to second。其中的日期由精度p來決定，p值為0~9，預設為2。小時為0~23，分鐘為0~59, 秒為0~59。其實日、時、分、秒也都可以單獨使用。單獨使用時，日期，時、分、秒的值都可以使用精度p來控制。

postgresql的interval中，也有類似的用法，而且用法比oracle靈活的多。

oracle interval day(p) to second

oracle的日期時間類型向postgresql的資料遷移過程相對來說簡單一些。由于postgresql的資料類型的極值超越oracle，是以，資料遷移過程中，隻要根據oracle的資料精度，在postgresql中選擇正确的資料類型。并留意一下二者sql寫法的不同，應該就能夠完整正确的遷移過來。

參考文檔：

postgresql 9.4.4 中文手冊：日期/時間類型

database sql language reference：data types