mysql时间戳转换成时间|极客笔记

mysql时间戳转换成时间

在数据库中，存储时间戳是比较常见的做法，因为时间戳是一种不受时区影响的时间表示方式，可以更方便地对时间进行比较和计算。但是，在实际业务中，我们通常需要将时间戳转换成人类可读的时间格式，以便更直观地展示给用户或者进行其他操作。本文将详细介绍如何在MySQL中进行时间戳和时间格式之间的转换。

时间戳和时间格式的概念

首先我们来了解一下时间戳和时间格式的概念。

时间戳（Timestamp）：时间戳是一种标记某个特定时间点的数字，在Unix系统中通常指自1970年1月1日零时以来的秒数。时间戳是一种十进制或者十六进制的数字，代表了一个具体的时间点。
时间格式（Datetime）：时间格式是人类可读的时间表示方式，通常包括年月日时分秒等信息，比如2022-01-01 12:00:00。

在MySQL中，时间戳通常以INT或者BIGINT类型存储，存储的是从1970-01-01 00:00:00到当前时间的秒数。而时间格式则以DATETIME类型存储，以常见的YYYY-MM-DD HH:mm:ss的格式表示时间。

时间戳转换成时间格式

FROM_UNIXTIME函数

MySQL提供了一个很方便的函数FROM_UNIXTIME用来将时间戳转换成时间格式。这个函数的用法非常简单，只需要将时间戳作为参数传入即可。

SELECT FROM_UNIXTIME(1648880000);

上面的示例中，1648880000是一个时间戳，运行这条SQL语句后，将会得到结果2022-03-02 08:00:00，即将时间戳转换成了人类可读的时间格式。

示例代码

-- 创建一个测试表
CREATE TABLE timestamp_test (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    timestamp_val BIGINT
);

-- 插入一条数据
INSERT INTO timestamp_test (timestamp_val) VALUES (1648880000);

-- 查询时间戳转换成时间格式后的结果
SELECT id, FROM_UNIXTIME(timestamp_val) AS time_format FROM timestamp_test;

运行以上SQL语句后，可以看到查询结果如下：

| id |     time_format    |
|----|---------------------|
| 1  | 2022-03-02 08:00:00 |

时间格式转换成时间戳

UNIX_TIMESTAMP函数

类似于FROM_UNIXTIME函数，MySQL也提供了一个函数UNIX_TIMESTAMP用来将时间格式转换成时间戳。这个函数的用法也很简单，只需要将时间格式作为参数传入即可。

SELECT UNIX_TIMESTAMP('2022-03-02 08:00:00');

上面的示例中，2022-03-02 08:00:00是一个时间格式，运行这条SQL语句后，将会得到结果1648880000，即将时间格式转换成了时间戳。

示例代码

-- 查询时间格式转换成时间戳后的结果
SELECT UNIX_TIMESTAMP('2022-03-02 08:00:00') AS timestamp_val;

运行以上SQL语句后，可以看到查询结果如下：

| timestamp_val |
|---------------|
| 1648880000    |

注意事项

在进行时间戳和时间格式的转换时，需要注意以下几点：

数据类型匹配：时间戳一般使用BIGINT类型存储，时间格式一般使用DATETIME类型存储。
时区问题：MySQL的时间戳是以秒数存储的，不受时区的影响。但是在转换成时间格式时可能需要考虑时区的问题。
时间范围：时间戳表示的时间范围较广，可以表示更早的时间，而时间格式一般只能表示到'1000-01-01 00:00:00'到'9999-12-31 23:59:59'范围内的时间。