MySQL时间戳转换日期的方法及示例|极客笔记

MySQL时间戳转换日期的方法及示例

1. 了解时间戳和日期的概念

在MySQL中，时间戳（Timestamp）是用整数表示的时间值，表示从公元1970年1月1日 00:00:00 UTC到某一特定时间点的秒数。而日期是以年、月、日的形式表示的时间。在实际的开发中，我们常常需要将时间戳转换为日期格式，以便更好地处理和展示数据。

2. 数据库表和字段设计

在开始之前，我们需要先创建一个MySQL数据库表，用来存储时间戳和日期数据。假设我们创建了一个名为timestamp_demo的数据库，并在其中创建了一个名为timestamps的表，表结构如下：

CREATE TABLE timestamps (
  id INT(11) NOT NULL AUTO_INCREMENT,
  timestamp_value INT(11) NOT NULL,
  date_value DATE,
  PRIMARY KEY (id)
);

3. 将时间戳转换为日期

要将时间戳转换为日期，我们可以使用MySQL提供的FROM_UNIXTIME()函数。该函数的作用是将给定的时间戳转换为日期时间格式。下面是FROM_UNIXTIME()函数的基本语法：

FROM_UNIXTIME(timestamp [,format])

其中，timestamp是要转换的时间戳，format是可选参数，用于指定转换后的日期时间格式。如果不指定format参数，将默认为YYYY-MM-DD HH:MM:SS的格式。

下面是一个简单的示例，演示如何将时间戳转换为日期：

SELECT FROM_UNIXTIME(1589817600);

-- 结果：2020-05-19 00:00:00

在这个示例中，我们将时间戳1589817600转换为日期2020-05-19 00:00:00。

使用FROM_UNIXTIME()函数后，你可以将其应用于表的查询语句中，以便在结果中显示转换后的日期。

4. 将日期转换为时间戳

与将时间戳转换为日期相反，如果你需要将日期转换为时间戳，可以使用MySQL提供的UNIX_TIMESTAMP()函数。该函数的作用是将给定的日期时间格式转换为时间戳。下面是UNIX_TIMESTAMP()函数的基本语法：

UNIX_TIMESTAMP(date)

其中，date是要转换的日期时间格式。

下面是一个简单的示例，演示如何将日期转换为时间戳：

SELECT UNIX_TIMESTAMP('2020-05-19 00:00:00');

-- 结果：1589817600

在这个示例中，我们将日期2020-05-19 00:00:00转换为时间戳1589817600。

使用UNIX_TIMESTAMP()函数后，你可以将其应用于表的插入语句或更新语句中，以便将日期转换为时间戳并存储在数据库中。

5. 查询并展示转换后的日期

下面是一个示例，演示如何查询数据库表中的时间戳，并将其转换为日期后展示出来：

SELECT timestamp_value, FROM_UNIXTIME(timestamp_value) AS date
FROM timestamps;

运行以上查询语句后，将会得到一个包含时间戳和转换后日期的结果集，如下所示：

+----------------+---------------------+
| timestamp_value| date                |
+----------------+---------------------+
| 1589817600     | 2020-05-19 00:00:00 |
| 1589904000     | 2020-05-20 00:00:00 |
| 1590574080     | 2020-05-27 08:21:20 |
| 1590656133     | 2020-05-28 06:42:13 |
| 1590742532     | 2020-05-29 07:35:32 |
+----------------+---------------------+

在这个示例中，我们查询了timestamps表中的时间戳，并使用FROM_UNIXTIME()函数将其转换为日期后进行展示。

6. 示例代码及运行结果

示例代码1：

SELECT FROM_UNIXTIME(1589817600);

运行结果1：

+---------------------+
| FROM_UNIXTIME(1589817600) |
+---------------------+
| 2020-05-19 00:00:00 |
+---------------------+

示例代码2：

SELECT UNIX_TIMESTAMP('2020-05-19 00:00:00');

运行结果2：

+-----------------------------------+
| UNIX_TIMESTAMP('2020-05-19 00:00:00') |
+-----------------------------------+
|                        1589817600 |
+-----------------------------------+

示例代码3：

SELECT timestamp_value, FROM_UNIXTIME(timestamp_value) AS date
FROM timestamps;

运行结果3：

+----------------+---------------------+
| timestamp_value| date                |
+----------------+---------------------+
| 1589817600     | 2020-05-19 00:00:00 |
| 1589904000     | 2020-05-20 00:00:00 |
| 1590574080     | 2020-05-27 08:21:20 |
| 1590656133     | 2020-05-28 06:42:13 |
| 1590742532     | 2020-05-29 07:35:32 |
+----------------+---------------------+

示例代码4：

INSERT INTO timestamps (timestamp_value, date_value) 
VALUES (1589817600, FROM_UNIXTIME(1589817600));

运行结果4：

Query OK, 1 row affected (0.01 sec)

示例代码5：

UPDATE timestamps
SET date_value = FROM_UNIXTIME(timestamp_value);

运行结果5：

Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

7. 总结

本文详细介绍了MySQL中将时间戳转换为日期的方法，并提供了相关的示例代码及运行结果。通过了解和运用这些方法，你可以更好地处理和展示数据库中的时间数据。

MySQL时间戳转换日期的方法及示例