MySQL：使用和增强'DATETIME”和'TIMESTAMP”-mysql教程-PHP中文网

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

王林

发布： 2024-07-19 12:40:08

原创

751 人浏览过

MySQL: Using and Enhancing `DATETIME` and `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');

登录后复制