MySQL：使用和增強'DATETIME”和'TIMESTAMP”

介紹

在 MySQL 中，DATETIME 和 TIMESTAMP 是儲存暫存值的常用資料類型。儘管兩者都用於儲存日期和時間，但它們之間存在影響其使用的根本差異。本文將探討 DATETIME 和 TIMESTAMP 之間的差異，以及如何克服 DATETIME 的一些限制以利用 TIMESTAMP 的優勢。

DATETIME 和 TIMESTAMP 之間的區別

範圍：

• DATETIME：可以儲存從「1000-01-01 00:00:00」到「9999-12-31 23:59:59」的值。
• TIMESTAMP：可以儲存從「1970-01-01 00:00:01」到「2038-01-19 03:14:07」的值。

貯存：

• DATETIME：以日期和時間格式按原樣存儲，不依賴時區。它需要 8 個位元組的儲存空間。
• TIMESTAMP：儲存為整數，表示自 1970-01-01 00:00:00 UTC 以來的秒數。它需要 4 個位元組的儲存空間。

時區：

• DATETIME：不依賴時區並按原樣儲存值。
• TIMESTAMP：受MySQL伺服器目前時區影響。插入或檢索值時，它們會在本機時間和 UTC 之間自動轉換。

自動更新：

• DATETIME：不支援修改行時直接自動更新。
• TIMESTAMP：可以設定為使用選項 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 修改行時自動更新值。

增強 DATETIME 的使用

要克服 DATETIME 資料類型的一些限制，您可以遵循以下策略：

1. 標準化時區

要解決 DATETIME 不遵守時區的問題，您可以將資料庫和應用程式層級的所有時態操作標準化為使用 UTC。

將資料庫設定為以 UTC 格式工作：

SET GLOBAL time_zone = '+00:00';
SET time_zone = '+00:00';

插入時將值轉換為 UTC：

INSERT INTO example_table (event_time) VALUES (CONVERT_TZ('2024-06-19 12:30:00', 'Your/Timezone', '+00:00'));

在檢索時將值從 UTC 轉換為當地時間：

SELECT CONVERT_TZ(event_time, '+00:00', 'Your/Timezone') as local_event_time FROM example_table;

2. 自動更新

要在修改行時自動更新 DATETIME 值，可以使用觸發器。

建立觸發器以在修改時更新欄位：

CREATE TRIGGER before_update_example_table
BEFORE UPDATE ON example_table
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

3. 使用預設值

要在插入時自動設定 DATETIME 值，您可以使用 NOW() 函數指派預設值。

設定預設欄位：

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4. 處理精度

如果需要更高精度的時間，可以使用 DATETIME(6) 或 TIMESTAMP(6) 來儲存高達微秒精度的時間。

創建微秒精度的表：

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

5. 申請中的處理時間

在儲存它們之前將所有時間轉換為 UTC，並在向使用者顯示它們時將它們轉換回本地時間，以確保您的應用程式正確處理時間。

PHP 中的範例：

// Setting the application time zone to UTC
date_default_timezone_set('UTC');

// Storing the current time as UTC
$current_time_utc = date('Y-m-d H:i:s');
$query = "INSERT INTO example_table (event_time) VALUES ('$current_time_utc')";

// Retrieving the time and converting it to local time
$event_time_utc = '2024-06-19 12:30:00';
$event_time_local = new DateTime($event_time_utc, new DateTimeZone('UTC'));
$event_time_local->setTimezone(new DateTimeZone('Your/Timezone'));
echo $event_time_local->format('Y-m-d H:i:s');

概括

• DATETIME：用於儲存不依賴時區的日期和時間。適合未來的活動或固定的行程。
• TIMESTAMP：用於追蹤相對於當前時區的時間。適合記錄資料插入或更新的時間。

增強DATETIME

• 標準化時區：使用UTC標準化時間。
• 自動更新：使用觸發器自動更新欄位。
• 預設值：設定 DATETIME 欄位的預設值。
• 精度：使用 DATETIME(6) 以獲得更高的精度。
• 應用程式中的處理時間：正確轉換 UTC 和本地時間之間的時間。

透過遵循這些策略，您可以在使用 DATETIME 的同時利用 TIMESTAMP 的優勢，從而更輕鬆有效地處理時間值。

以上是MySQL：使用和增強'DATETIME”和'TIMESTAMP”的詳細內容。更多資訊請關注PHP中文網其他相關文章！