SQL时间戳转换成日期|极客笔记

SQL时间戳转换成日期

1. 介绍

在数据库中，时间戳是一种用来表示日期和时间的数据类型，通常以整数或者浮点数的形式存在。而日期是人们最常用的时间表示方式，一般格式为年-月-日，如2021-01-01。

在一些情况下，我们需要将数据库中存储的时间戳数据转换成日期格式，以便更好地展示或者与其他日期进行比较。本文将详细介绍如何在SQL中将时间戳转换成日期。

2. 时间戳与日期的转换

在SQL中，常见的时间戳数据类型有UNIX时间戳和UNIX毫秒时间戳。这两种时间戳都可以通过以下方法进行转换成日期：

2.1. UNIX时间戳转换成日期

UNIX时间戳表示从”1970-01-01 00:00:00″到当前时间的秒数。假设数据库中存储的时间戳字段为timestamp_unix，我们可以使用以下语句将其转换成日期：

SELECT timestamp_unix, FROM_UNIXTIME(timestamp_unix) AS date 
FROM table_name;

其中，table_name是包含时间戳字段的表名，timestamp_unix是需要转换的时间戳字段。

运行结果示例：

+----------------+-------------------+
| timestamp_unix | date              |
+----------------+-------------------+
| 1621210529     | 2021-05-17 08:42:09 |
| 1621242921     | 2021-05-17 17:42:01 |
| 1621269354     | 2021-05-18 00:42:34 |
+----------------+-------------------+

2.2. UNIX毫秒时间戳转换成日期

UNIX毫秒时间戳表示从”1970-01-01 00:00:00″到当前时间的毫秒数。与UNIX时间戳类似，我们可以使用以下语句将其转换成日期：

SELECT timestamp_millis, FROM_UNIXTIME(timestamp_millis/1000) AS date 
FROM table_name;

其中，table_name是包含时间戳字段的表名，timestamp_millis是需要转换的时间戳字段。

运行结果示例：

+-----------------+-------------------+
| timestamp_millis | date              |
+-----------------+-------------------+
| 1621210529567   | 2021-05-17 08:42:09 |
| 1621242921954   | 2021-05-17 17:42:01 |
| 1621269354089   | 2021-05-18 00:42:34 |
+-----------------+-------------------+

3. 时间戳格式化

除了将时间戳转换成日期，我们还可以通过格式化来调整日期的显示格式。

3.1. 格式化日期

在SQL中，可以使用DATE_FORMAT函数来格式化日期。以下是一些常见的日期格式化选项：

%Y：四位年份；
%y：两位年份；
%m：带前导零的月份；
%c：无前导零的月份；
%d：带前导零的日期；
%e：无前导零的日期；
%H：带前导零的小时（24小时制）；
%h：无前导零的小时（12小时制）；
%i：分钟；
%s：秒；
%p：小写的”am”或”pm”。

以下是一个将时间戳字段格式化为”年-月-日时:分:秒”的示例：

SELECT timestamp_unix, DATE_FORMAT(FROM_UNIXTIME(timestamp_unix), '%Y-%m-%d %H:%i:%s') AS formatted_date 
FROM table_name;

运行结果示例：

+----------------+-------------------+
| timestamp_unix | formatted_date    |
+----------------+-------------------+
| 1621210529     | 2021-05-17 08:42:09 |
| 1621242921     | 2021-05-17 17:42:01 |
| 1621269354     | 2021-05-18 00:42:34 |
+----------------+-------------------+

3.2. 格式化日期区间

在一些情况下，我们可能需要将日期字段按照一定的区间进行格式化，例如按年、按月或者按日。SQL提供了一些内置函数来支持这些需求。

YEAR：提取年份；
MONTH：提取月份；
DAY：提取日期；
HOUR：提取小时；
MINUTE：提取分钟；
SECOND：提取秒。

以下是一个将时间戳字段按照年份格式化的示例：

SELECT timestamp_unix, YEAR(FROM_UNIXTIME(timestamp_unix)) AS year 
FROM table_name;

运行结果示例：

+----------------+------+
| timestamp_unix | year |
+----------------+------+
| 1621210529     | 2021 |
| 1621242921     | 2021 |
| 1621269354     | 2021 |
+----------------+------+