SQL~~About the TIMESTAMP in PostgreSQL and MySQL

關於 TIMESTAMP 資料型態在 PostgreSQL (v7.2 or later) 和 MySQL (v4.1 or later) 的差異。

TIMESTAMP in PostgreSQL (v7.2 or later)

在 PostgreSQL 中，提供 TIMESTAMP, DATE, TIME, INTERVAL 這 4 種時間資料型態。但要注意的是， PostgreSQL 不提供常見於其他 DMBS 中的 DATETIME 資料型態，也許是認為 TIMESTAMP 比 DATETIME 更好用吧， TIMESTAMP 完全可以替代 DATETIME 。

在 PostgreSQL 中， TIMESTAMP 提供了相當長的時間範圍，可以表示的時間範圍從西元前 4713 年 到西元 5874897 年。PostgreSQL 使用 8bytes 儲存 TIMESTAMP 的資料，如果設計者只是要儲存日期而不需要時間 (例如出生日期) ，那麼應該使用 DATE 資料型態，DATE 型態只需要用 4 bytes 儲存。

設計者在表示時間資料時，是用一個格式化字串來表示 [表示時間的字串格式很多，但個人建議設計者使用 ISO8601 的格式來表示，即 YYYY-MM-DD hh:mm:ss (年-月-日 時:分:秒)，文化差異性最低。在 C/C++ 函數中，即 strftime(s, max, "%Y-%m-%d %H:%M:%S", gmtime());，在 PHP 中，即 gmdate("Y-m-d H:i:s");]，例如 '2004-10-19 10:23:54' ，而且 PostgreSQL 會假設這個字串所表示的是 UTC 時間。只有當字串中包含 '+' 或 '-' 時， PostgreSQL 才會將字串所表示的時間，視為帶時區的時間，例如 '2004-10-19 10:23:54+08' (UTC時間再加8小時，亦即中原／臺北時間)。

在 PostgreSQL v7.3 以前， TIMESTAMP 資料型態預設為帶時區標示 (timestamp with time zone) 。以後則改為不帶時區標示，以相容於 SQL 標準。然而 PostgreSQL 總是使用 UTC (GMT) 時間儲存 TIMESTAMP 資料，只在輸出時，會依據 timezone 換算成本地時間。在輸出時，是以 ISO8601 的格示顯示時間，例如 '2004-10-19 10:23:54' 。若欄位的型態為帶時區標示的 TIMESTAMP ，則輸出 '2004-10-19 10:23:54+08' 。

在儲存資料到 TIMESTAMP 型態的欄位時，要注意時間的換算。如果欄位是不帶時區的 TIMESTAMP (TIMESTAMP without time zone) ，而欲存入的時間字串中包含時區標示，則 PostgreSQL 會忽略時區標示。因此若你讀取了一個本地時間，而欲將此值存入一個不帶時區的時間欄位前，要先將時間值換算成 UTC 時間。

create table time_table ( time1 timestamp);
insert into time_table values ( '2006-03-01 12:00:00');
insert into time_table values ( '2006-03-01 12:00:00+08');
select * from time_table;


2006-03-01 12:00:00
2006-03-01 12:00:00

注意第二列的結果，顯示 PostgreSQL 忽略了 +08 這個時區標示。

TIMESTAMP in MySQL (v4.1 or later)

在 MySQL 中，提供 DATETIME, DATE, TIMESTAMP, TIME, YEAR 的時間資料型態。

MySQL 雖然也提供 TIMESTAMP 資料型態，但行為和 PostgreSQL 並不相同。首先， MySQL 的 TIMESTAMP 表示範圍為西元 1970 年1月1日零時，到西元 2037 年。如果使用者要表示的資料超過這範圍 (通常是小於 1970 年，例如出生日期)，則要使用 DATETIME 資料型態。再者， MySQL 在 4.1 版後，才將輸出的顯示格式改為 ISO8601 ('YYYY-MM-DD hh:mm:ss') 的型式，在此之前，是顯示為 'YYYYMMDDhhmmss' 。

在 MySQL 中，同樣是將時間資料以 UTC 時間儲存，在輸出時才換算成本地時間。由於 TIMESTAMP 在 SQL 中，主要是作為事件發生記錄、時間戳記之用途，因此一律以 UTC 時間儲存，在內部處理時較為便捷。

TIMESTAMP is MS SQL Server 2000

在 MS SQL Server 中， TIMESTAMP 型態的行為不同於 SQL-92 標準[根據 MS SQL Server 手冊中的說法「未來版本的 Microsoft® SQL Server™ 將會修改 Transact-SQL timestamp 資料型別的功能，使其與標準定義的功能一致。屆時，現有的 timestamp 資料型別將會被 rowversion 資料型別所取代。」]，反而是 MS SQL Server 的 DATETIME 型態和 SQL-92 的 TIMESTAMP 相同。MS SQL Server 的 TIMESTAMP 型別之作用，相近於 PostgreSQL 的 SERIAL 型別；而 MS SQL Server 的 DATETIME 型別和 PostgreSQL 的 TIMESTAMP 可說是相等的。MS SQL Server 的 DATETIME 型別可表達範圍從 1753 年 1 月 1 日到 9999年 12 月 31 日。