Oracle Timestamp型別

在本教學中將學習Oracle TIMESTAMP資料型別以及如何在Oracle資料庫中有效處理TIMESTAMP資料。

Oracle TIMESTAMP資料型別簡介

TIMESTAMP資料型別用於儲存日期和時間資料，包括年，月，日，時，分和秒。

另外，它儲存小數秒，它不是由DATE資料型別儲存的。

要定義TIMESTAMP列，請使用以下語法：

column_name TIMESTAMP[(fractional_seconds_precision)]

fractional_seconds_precision指定SECOND欄位小數部分的位數。它的範圍從0到9，這意味著可以使用TIMESTAMP資料型別來儲存到納秒的精度。
如果省略fractional_seconds_precision，則預設為6。

以下表示式說明了如何定義TIMESTAMP列：

...
started_at TMESTAMP(2),
...

在此範例中，started_at列是一個TIMESTAMP列，其分數秒精度設定為微秒。

Oracle TIMESTAMP文字

要指定TIMESTAMP文字，請使用以下格式：

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

以下範例說明了TIMESTAMP文字：

TIMESTAMP '1999-12-31 23:59:59.10'

有關如何構造格式字串的詳細資訊，請檢視Oracle日期格式。

Oracle TIMESTAMP範例

首先，建立一個名為logs的新表，其中包含一個TIMESTAMP列用於演示。

CREATE TABLE logs (
    log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    message VARCHAR(2) NOT NULL,
    logged_at TIMESTAMP (2) NOT NULL,
    PRIMARY KEY (log_id)
);

其次，將新行插入到logs表中：

INSERT INTO logs (
    message,
    logged_at
    )
VALUES (
    'Invalid username/password for root user',
    LOCALTIMESTAMP(2)
    );

INSERT INTO logs (
    message,
    logged_at
    )
VALUES (
    'User root logged in successfully',
    LOCALTIMESTAMP(2)
    );

在這個例子中，使用LOCALTIMESTAMP(2)函式中得到當前的本地時間戳，其精確度秒為微秒，並將該值插入到logs表的logs_at列中。

第三，從紀錄檔表中查詢TIMESTAMP資料：

SELECT log_id,
       message,
       logged_at
FROM logs;

執行上面查詢語句，得到下面結果 -

格式化TIMESTAMP值

要更改TIMESTAMP值的輸出，可以使用TO_CHAR()函式，將TIMESTAMP值或列的名稱作為第一個引數，將格式字串作為第二個引數。

以下語句使用TO_CHAR()函式來格式化logged_at列中的值：

SELECT message,
    TO_CHAR(logged_at, 'MONTH DD, YYYY "at" HH24:MI')
FROM logs;

執行上面查詢語句，得到下面結果 -

提取TIMESTAMP元件

要提取TIMESTAMP元件(如年，月，日，小時，分鐘和秒)，請使用EXTRACT()函式：

EXTRACT( component FROM timestamp);

如以下面的語句範例：

SELECT 
    message,
    EXTRACT(year FROM logged_at) year,
    EXTRACT(month FROM logged_at) month,
    EXTRACT(day FROM logged_at) day,
    EXTRACT(hour FROM logged_at) hour,
    EXTRACT(minute FROM logged_at) minute,
    EXTRACT(second FROM logged_at) second
FROM 
    logs;

注意到NLS_DATE_LANGUAGE引數決定日期名稱(Thursday)，月份名稱(August)以及TIMESTAMP縮寫(THU，AUG)的語言。

預設的TIMESTAMP格式

當字元型別的值被轉換為TIMESTAMP資料型別時，Oracle使用NLS_TIMESTAMP_FORMAT引數來控制預設時間戳格式。

以下語句返回Oracle資料庫系統中當前的預設時間戳格式：

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_TIMESTAMP_FORMAT';

執行上面查詢語句，將得到以下結果 -

DD-MON-RR HH.MI.SSXFF AM

例如，假設要將新行以logs_attane值插入到logs表中，則使用TO_TIMESTAMP()函式將字串轉換為TIMESTAMP值，如下所示：

INSERT INTO logs (
    message,
    logged_at
    )
VALUES (
    'Test default Oracle timestamp format',
    TO_TIMESTAMP('03-AUG-17 11:20:30.45 AM')
    );

請注意，時間戳值 - '03-AUG-17 11:20:30.45 AM'遵循標準時間戳格式。

下面來驗證結果：

SELECT
  log_id, message, logged_at
FROM
  logs;

執行上面範例程式碼，得到以下結果 -

如果想使用其他時間戳格式而不是預設格式，則可以使用ALTER SESSION SET語句來執行此操作。

在本教學中，您已經了解了Oracle TIMESTAMP資料型別以及如何有效地處理時間戳。