面试题：MySQL中日期和时间类型的常见转换函数及应用场景

Question

在MySQL中，常用的日期和时间类型转换函数有哪些？请举例说明每个函数在实际项目中的应用场景，比如如何将日期格式从'YYYY - MM - DD'转换为'DD - Mon - YYYY'，或者将时间戳转换为日期时间格式等。

Answer 1

常用日期和时间类型转换函数及应用场景

1. DATE_FORMAT()
   • 描述：用于将日期值按照指定的格式进行格式化输出。
   • 应用场景：在报表生成中，可能需要将日期按照特定格式展示。例如，要将日期格式从'YYYY - MM - DD'转换为'DD - Mon - YYYY'，可以这样使用：

SELECT DATE_FORMAT('2023 - 10 - 05', '%d - %b - %Y');

- **结果**：`05 - Oct - 2023`

2. STR_TO_DATE() - 描述：将字符串按照指定的格式转换为日期类型。 - 应用场景：当从外部数据源获取到的日期是以字符串形式存在，且格式与数据库中日期字段格式不一致时。例如，外部数据是'10/05/2023'格式，要转换为数据库中的'YYYY - MM - DD'格式插入表中：

SELECT STR_TO_DATE('10/05/2023', '%m/%d/%Y');

- **结果**：`2023 - 10 - 05`

3. UNIX_TIMESTAMP() - 描述：获取日期或日期时间的时间戳（从1970 - 01 - 01 00:00:00 UTC到指定时间的秒数）。如果没有参数，返回当前时间的时间戳。 - 应用场景：在记录操作时间戳等场景中，例如记录用户登录时间的时间戳：

SELECT UNIX_TIMESTAMP('2023 - 10 - 05 12:30:00');

- **结果**：`1696499400`

4. FROM_UNIXTIME() - 描述：将时间戳转换为日期时间格式。 - 应用场景：在需要将时间戳还原为可读日期时间格式展示给用户时。例如将时间戳1696499400转换为日期时间格式：

SELECT FROM_UNIXTIME(1696499400);

- **结果**：`2023 - 10 - 05 12:30:00`

5. CONVERT_TZ() - 描述：用于在不同时区之间转换日期时间。 - 应用场景：如果应用程序涉及多个时区的用户，要将用户所在时区的时间转换为服务器所在时区的时间。例如，将纽约时间（America/New_York）转换为北京时间（Asia/Shanghai）：

SELECT CONVERT_TZ('2023 - 10 - 05 12:00:00', 'America/New_York', 'Asia/Shanghai');

- **结果**：根据时差转换后的北京时间。具体结果会因当时的夏令时等因素有所不同。