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 中国語 Web サイトの他の関連記事を参照してください。