MySQL 时间格式化

1. 引言

在实际的软件开发中，经常会涉及到时间的处理和显示。MySQL 作为一种常用的数据库管理系统，也提供了丰富的函数来处理时间。本文将介绍在 MySQL 中如何对时间进行格式化的操作。

2. 时间类型

在 MySQL 中，时间类型主要有以下几种：

DATE：仅包含日期，格式为’YYYY-MM-DD’。
TIME：仅包含时间，格式为’hh:mm:ss’。
DATETIME：包含日期和时间，格式为’YYYY-MM-DD hh:mm:ss’。
TIMESTAMP：包含日期和时间，格式为’YYYY-MM-DD hh:mm:ss’，范围从 ‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’。
YEAR：仅包含年份，格式为’YYYY’。

3. 函数介绍

MySQL 提供了一些函数来对时间进行格式化操作。下面是几个常用的函数的介绍。

3.1 DATE_FORMAT

DATE_FORMAT 函数可以将日期按照指定的格式转换为字符串。它的语法如下：

DATE_FORMAT(date, format)

其中，date 是需要格式化的日期，format 是指定的格式。下面是一些常用的格式：

‘%Y’：四位数的年份（例如：’2022’）。
‘%y’：两位数的年份（例如：’22’）。
‘%m’：两位数的月份（从 01 到 12）。
‘%d’：两位数的日期（从 01 到 31）。
‘%h’：12 小时制的小时（从 01 到 12）。
‘%H’：24 小时制的小时（从 00 到 23）。
‘%i’：两位数的分钟（从 00 到 59）。
‘%s’：两位数的秒数（从 00 到 59）。

下面是一个示例代码：

SELECT DATE_FORMAT('2022-04-13', '%Y-%m-%d') AS formatted_date;

运行结果为：

+----------------+
| formatted_date |
+----------------+
| 2022-04-13     |
+----------------+

3.2 TIME_FORMAT

TIME_FORMAT 函数可以将时间按照指定的格式转换为字符串。它的语法如下：

TIME_FORMAT(time, format)

其中，time 是需要格式化的时间，format 是指定的格式。格式的选项和 DATE_FORMAT 函数相似。

下面是一个示例代码：

SELECT TIME_FORMAT('14:30:00', '%h:%i %p') AS formatted_time;

运行结果为：

+----------------+
| formatted_time |
+----------------+
| 02:30 PM       |
+----------------+

3.3 NOW

NOW 函数返回当前日期和时间。它的语法如下：

NOW()

下面是一个示例代码：

SELECT NOW() AS current_datetime;

运行结果为：

+---------------------+
| current_datetime    |
+---------------------+
| 2022-04-13 09:40:25 |
+---------------------+

3.4 CURDATE

CURDATE 函数返回当前日期。它的语法如下：

CURDATE()

下面是一个示例代码：

SELECT CURDATE() AS current_date;

运行结果为：

+--------------+
| current_date |
+--------------+
| 2022-04-13   |
+--------------+

3.5 CURTIME

CURTIME 函数返回当前时间。它的语法如下：

CURTIME()

下面是一个示例代码：

SELECT CURTIME() AS current_time;

运行结果为：

+--------------+
| current_time |
+--------------+
| 09:40:25     |
+--------------+

4. 示例

下面是一个示例，演示如何使用上述函数对时间进行格式化操作。

4.1 创建测试表

首先，我们创建一个测试表来存储时间数据：

CREATE TABLE time_test (
  id INT PRIMARY KEY,
  date_col DATE,
  time_col TIME,
  datetime_col DATETIME,
  timestamp_col TIMESTAMP,
  year_col YEAR
);

4.2 插入测试数据

然后，我们插入一些测试数据：

INSERT INTO time_test (id, date_col, time_col, datetime_col, timestamp_col, year_col)
VALUES (1, '2022-04-13', '14:30:00', '2022-04-13 14:30:00', '2022-04-13 14:30:00', '2022');

INSERT INTO time_test (id, date_col, time_col, datetime_col, timestamp_col, year_col)
VALUES (2, '2021-01-01', '08:00:00', '2021-01-01 08:00:00', '2021-01-01 08:00:00', '2021');

4.3 查询结果

最后，我们查询表中的数据并对时间进行格式化操作：

SELECT 
  id,
  DATE_FORMAT(date_col, '%Y-%m-%d') AS formatted_date,
  TIME_FORMAT(time_col, '%h:%i %p') AS formatted_time,
  DATE_FORMAT(datetime_col, '%Y-%m-%d %H:%i:%s') AS formatted_datetime,
  DATE_FORMAT(timestamp_col, '%Y-%m-%d %H:%i:%s') AS formatted_timestamp,
  DATE_FORMAT(timestamp_col, '%Y-%m-%d') AS formatted_timestamp_date,
  CURDATE() AS current_date,
  CURTIME() AS current_time,
  NOW() AS current_datetime
FROM time_test;

运行结果为：

+----+----------------+----------------+---------------------+---------------------+------------------------+--------------+--------------+---------------------+
| id | formatted_date | formatted_time | formatted_datetime  | formatted_timestamp | formatted_timestamp_date | current_date | current_time | current_datetime    |
+----+----------------+----------------+---------------------+---------------------+------------------------+--------------+--------------+---------------------+
| 1  | 2022-04-13     | 02:30 PM       | 2022-04-13 14:30:00 | 2022-04-13 14:30:00 | 2022-04-13             | 2022-04-13   | 09:40:25     | 2022-04-13 09:40:25 |
| 2  | 2021-01-01     | 08:00 AM       | 2021-01-01 08:00:00 | 2021-01-01 08:00:00 | 2021-01-01             | 2022-04-13   | 09:40:25     | 2022-04-13 09:40:25 |
+----+----------------+----------------+---------------------+---------------------+------------------------+--------------+--------------+---------------------+

5. 总结

本文介绍了在 MySQL 中对时间进行格式化的操作。我们可以使用 DATE_FORMAT 函数对日期进行格式化，使用 TIME_FORMAT 函数对时间进行格式化。此外，MySQL 还提供了 NOW、CURDATE 和 CURTIME 函数可用于获取当前日期和时间。对时间进行格式化可以方便地满足不同的需求，使得时间的显示更加符合要求。这些函数的使用可以帮助我们在数据库操作中更好地处理和展示时间信息。

需要注意的是，MySQL 中的时间格式化操作通常是在查询语句中进行的，而不是在数据存储时进行。这样可以根据需要随时改变时间的显示格式，而不需要修改数据本身。另外，MySQL 中还提供了其他的时间处理函数，如DATE_ADD、DATE_SUB、TIMESTAMPDIFF等，可以用于时间的计算和比较。

在实际的开发中，我们经常需要将时间格式化为特定的字符串，比如在网页或移动应用中将时间显示为”年-月-日时:分:秒”的格式。这时，我们可以使用DATE_FORMAT函数将时间转换为所需的字符串格式，然后将其嵌入到网页或应用程序中。

另外，MySQL 中还支持自定义日期时间格式。通过设置日期时间格式化字符串，我们可以实现各种不同的格式需求。例如，我们可以将时间显示为”2022年4月13日”或”1月1日，2021年”等特定格式。

总之，MySQL 提供了丰富的时间处理函数和格式化选项，可以满足不同时间处理和显示的需求。合理地使用这些函数，可以使时间的处理更加灵活、便捷，并且以适当的方式展示时间信息。