MySQL PHP, MySQL and Time Zones

在开发Web应用程序时，经常需要在PHP中使用MySQL数据库。该组合已成为一个非常强大和流行的选择。但是，当在不同的时区中工作时，由于Web应用程序需要展示当前时间，常见的问题在于如何在MySQL中存储和检索日期和时间，以及如何处理时区的问题。在这篇文章中，我们将探讨如何在PHP和MySQL中管理时区，以便确保我们正确地显示日期和时间。

阅读更多：MySQL 教程

PHP中的时区

PHP具有非常强大的时区支持，使得处理用户的本地时间和存储在服务器上的UTC时间变得非常简单。该支持包括一个内置的时区数据库，并提供了各种函数来在不同时区之间进行转换。

时区标识符

时区标识符是一个字符串，指定从协调世界时（UTC）中的UTC偏移量和夏令时观测方式细节，到本地时区所需要的信息。时区标识符是一个很好的选择，因为它们不会受夏令时观测方式的影响而发生变化。

时区标识符可以通过date_default_timezone_set()函数设置，用来指定应用程序中所使用的时区。

date_default_timezone_set('America/Los_Angeles');

时间戳

Unix 时间戳是一个用自1970年1月1日UTC开始经过的秒数表示的时间。PHP使用这种形式表示本地时间和UTC时间，并通过使用date()函数和gmdate()函数将时间戳格式化为可读字符串。

// 获取当前本地时间的时间戳
localtime = time();

// 将时间戳格式化为可读字符串localtime_string = date('Y-m-d H:i:s', localtime);

// 获取当前UTC时间的时间戳utc_time = gmdate('Y-m-d H:i:s', time());

日期和时间对象

PHP还提供了一个 DateTime API，用于处理日期和时间。使用这个API，可以很容易地向日期和时间对象添加或减去时间段，查询特定的日期或时间，并以各种格式表示日期和时间。使用对象，错误发生的概率比使用字符串或数值类型的日期和时间表示要小得多。

$datetime = new DateTime('2019-05-01 14:00:00', new DateTimeZone('America/Los_Angeles'));
$datetime->add(new DateInterval('PT2H30M')); // 增加2小时30分钟
$datetime->format('Y-m-d H:i:s'); // '2019-05-01 16:30:00'

MySQL中的时区

虽然PHP的时区支持非常强大，但是当与MySQL结合使用时，需要非常小心，以确保在数据库中正确地存储和检索日期和时间。MySQL 5.6或更高版本中引入了两个新功能。第一个功能允许将数据库服务和MySQL客户端配置为使用一致的时区，而第二个则允许为每个连接设置时区。

配置MySQL

在MySQL中，时区以定制的时区偏移量表示，例如’+08:00’，它表示相对于UTC+8小时的偏移量。在MySQL 5.6或更高版本中，default_time_zone系统变量用于指定MySQL服务器所在的时区。

SET @@global.time_zone = '+08:00';

-- 或者在my.cnf中添加如下配置
[mysqld]
default_time_zone = 'Asia/Shanghai'

默认情况下，MySQL将数据存储为本地时间，并在需要时自动转换为UTC时间。然而，如果在连接到数据库时使用的客户端时区与MySQL服务器不同，或者需要在MySQL中处理特定的时区，可能需要额外的配置。

配置连接时区

当PHP应用程序与MySQL服务器交互时，需要配置连接的时区，以确保正确的日期和时间被存储和检索。在MySQL中，可以使用SET time_zone语句来设置连接的时区。

// 创建PDO连接并设置时区
dsn = 'mysql:host=localhost;dbname=test;charset=UTF8';username = 'root';
password = '';options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = \'+08:00\'',
    PDO::MYSQL_ATTR_FOUND_ROWS => true
);

pdo = new PDO(dsn, username,password, $options);

在上面的代码中，我们为PDO连接设置了一个SET time_zone语句，将时区设置为UTC+8小时。这意味着我们可以在应用程序中使用本地时间，而在MySQL中存储UTC时间，以及正确地检索结果。

存储日期和时间

在MySQL中存储日期和时间，可以使用多种数据类型。通常使用DATETIME和TIMESTAMP两种类型，分别存储日期和时间。

-- 用 DATETIME 存储日期和时间
CREATE TABLE `example_datetime` (
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL
);

-- 用 TIMESTAMP 存储日期和时间
CREATE TABLE `example_timestamp` (
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

在上面的代码示例中，我们创建了两个表格，一个使用DATETIME类型，另一个使用TIMESTAMP类型。必须注意的是，TIMESTAMP类型存储的日期和时间是自动转换为UTC时间，而使用DATETIME类型没有这样的自动转换。

检索日期和时间

在MySQL中检索日期和时间，必须考虑到所使用的时区以及所检索的日期和时间是否为UTC时间。为了确保正确地检索日期和时间，在查询中需要将日期和时间转换为所需的时区。

// 检索本地时间
stmt =pdo->prepare('SELECT * FROM example_datetime WHERE created_at >= :start AND created_at <= :end');
start = new DateTime('2019-01-01 00:00:00', new DateTimeZone('Asia/Shanghai'));end = new DateTime('2019-01-31 23:59:59', new DateTimeZone('Asia/Shanghai'));
stmt->bindValue(':start',start->format('Y-m-d H:i:s'), PDO::PARAM_STR);
stmt->bindValue(':end',end->format('Y-m-d H:i:s'), PDO::PARAM_STR);
stmt->execute();

// 检索UTC时间stmt = pdo->prepare('SELECT * FROM example_timestamp WHERE created_at >= CONVERT_TZ(:start, \'UTC\', \'+08:00\') AND created_at <= CONVERT_TZ(:end, \'UTC\', \'+08:00\')');start = new DateTime('2019-01-01 00:00:00', new DateTimeZone('UTC'));
end = new DateTime('2019-01-31 23:59:59', new DateTimeZone('UTC'));stmt->bindValue(':start', start->format('Y-m-d H:i:s'), PDO::PARAM_STR);stmt->bindValue(':end', end->format('Y-m-d H:i:s'), PDO::PARAM_STR);stmt->execute();

在上面的代码示例中，在检索本地时间时，我们将所需的日期和时间转换为Asia/Shanghai的时区。而在检索UTC时间时，我们使用MySQL的CONVERT_TZ()函数将日期和时间转换为UTC时间。

总结

在Web应用程序中，日期和时间通常是一个重要的方面。在PHP中，我们可以使用内置的时区支持和DateTime API来管理日期和时间。在MySQL中，我们需要小心地处理时区问题，并在存储和检索日期和时间时进行适当的转换。通过正确地处理时区问题，我们可以确保正确地显示日期和时间，从而使我们的Web应用程序更加可靠和可预测。