SQL 时间戳与日期：轻松转换教程 – wiki大全

---

SQL 时间戳与日期：轻松转换教程

摘要: 在数据库操作中，日期和时间数据类型扮演着至关重要的角色。本教程将深入探讨 SQL 中 TIMESTAMP（时间戳）和 DATE（日期）数据类型之间的转换技巧，涵盖从提取日期到格式化、再到时区处理等常见场景，并提供主流数据库（PostgreSQL, MySQL, SQL Server, Oracle）的实用示例，助您轻松驾驭日期时间操作。

引言

在 SQL 数据库中，TIMESTAMP 和 DATE 是两种基本的时间数据类型。DATE 类型仅存储年、月、日信息，不包含时间部分；而 TIMESTAMP 类型则包含了完整的日期和时间信息，甚至可能包含毫秒和时区详情。

理解并能够灵活地在这些数据类型之间进行转换，对于数据分析、报表生成、数据清洗以及确保数据一致性至关重要。例如，您可能需要：
* 从一个精确到秒的时间戳中，仅提取出日期部分用于按天进行数据聚合。
* 将数据库中存储的日期时间数据，以用户友好的格式（如“YYYY年MM月DD日”）显示在应用程序中。
* 将用户输入的各种格式的日期字符串，准确地转换为数据库可识别的日期时间类型进行存储。
* 在全球化应用中，处理不同时区下的日期时间转换。

本教程将详细介绍这些转换方法。

主要内容

1. 从时间戳中提取日期

这是最常见的需求之一，即从包含时间的时间戳数据中，仅获取其日期部分，忽略具体时间。

• PostgreSQL:
  PostgreSQL 提供了多种简洁的方式来完成此操作。
  “`sql
  — 使用 ::DATE 类型转换操作符
  SELECT timestamp_column::DATE AS converted_date FROM your_table;

  — 使用 DATE() 函数
  SELECT DATE(timestamp_column) AS converted_date FROM your_table;
  “`

• MySQL:
  MySQL 也支持使用 CAST() 或 DATE() 函数。
  “`sql
  — 使用 CAST() 函数
  SELECT CAST(timestamp_column AS DATE) AS converted_date FROM your_table;

  — 使用 DATE() 函数
  SELECT DATE(timestamp_column) AS converted_date FROM your_table;
  “`

• SQL Server:
  SQL Server 提供 CAST() 和 CONVERT() 函数。
  “`sql
  — 使用 CAST() 函数
  SELECT CAST(timestamp_column AS DATE) AS converted_date FROM your_table;

  — 使用 CONVERT() 函数
  SELECT CONVERT(DATE, timestamp_column) AS converted_date FROM your_table;
  ``
**注意:** 在 SQL Server 中，TIMESTAMP数据类型是一个用于行版本控制的旧有机制，并不存储日期或时间信息。请使用DATETIME,DATETIME2,SMALLDATETIME或DATE` 类型来存储日期时间数据。

• Oracle:
  Oracle 可以使用 TRUNC() 或 CAST() 函数。
  “`sql
  — 使用 TRUNC() 函数截断时间部分
  SELECT TRUNC(timestamp_column) AS converted_date FROM your_table;

  — 使用 CAST() 函数
  SELECT CAST(timestamp_column AS DATE) AS converted_date FROM your_table;
  “`

2. 日期/时间戳格式化为字符串

当您需要以特定格式显示日期或时间戳时，格式化函数非常有用。

• PostgreSQL:
  使用 TO_CHAR() 函数进行格式化。
  “`sql
  — 格式化为 ‘YYYY-MM-DD HH:MI:SS’
  SELECT TO_CHAR(timestamp_column, ‘YYYY-MM-DD HH24:MI:SS’) AS formatted_string FROM your_table;

  — 仅格式化日期部分
  SELECT TO_CHAR(timestamp_column, ‘YYYY-MM-DD’) AS formatted_date_string FROM your_table;
  ``
常用格式模式包括YYYY(四位年份),MM(两位月份),DD(两位日期),HH24(24小时制小时),MI(分钟),SS` (秒)。

• MySQL:
  使用 DATE_FORMAT() 函数进行格式化。
  “`sql
  — 格式化为 ‘YYYY-MM-DD HH:MI:SS’
  SELECT DATE_FORMAT(timestamp_column, ‘%Y-%m-%d %H:%i:%s’) AS formatted_string FROM your_table;

  — 仅格式化日期部分
  SELECT DATE_FORMAT(timestamp_column, ‘%Y-%m-%d’) AS formatted_date_string FROM your_table;
  ``
常用格式说明符包括%Y(四位年份),%m(两位月份),%d(两位日期),%H(24小时制小时),%i(分钟),%s` (秒)。

• SQL Server:
  使用 CONVERT() 函数结合样式代码进行格式化。
  “`sql
  — 使用样式 120 (yyyy-mm-dd hh:mi:ss)
  SELECT CONVERT(VARCHAR(20), timestamp_column, 120) AS formatted_string FROM your_table;

  — 仅格式化日期部分，使用样式 23 (yyyy-mm-dd)
  SELECT CONVERT(VARCHAR(10), timestamp_column, 23) AS formatted_date_string FROM your_table;
  “`
  SQL Server 提供了丰富的样式代码，以满足各种格式需求。

• Oracle:
  使用 TO_CHAR() 函数进行格式化。
  “`sql
  — 格式化为 ‘YYYY-MM-DD HH:MI:SS’
  SELECT TO_CHAR(timestamp_column, ‘YYYY-MM-DD HH24:MI:SS’) AS formatted_string FROM your_table;

  — 仅格式化日期部分
  SELECT TO_CHAR(timestamp_column, ‘YYYY-MM-DD’) AS formatted_date_string FROM your_table;
  “`
  Oracle 的格式模型与 PostgreSQL 类似。

3. 字符串转换为日期/时间戳

当从外部源（如用户输入、文件导入）获取日期时间数据时，通常是字符串格式，需要将其转换为适当的日期时间类型才能进行数据库存储或计算。

• PostgreSQL:

  • TO_TIMESTAMP() 用于将字符串转换为 TIMESTAMP。
    sql
SELECT TO_TIMESTAMP('2023-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
  • TO_DATE() 用于将字符串转换为 DATE。
    sql
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD');

• MySQL:
  MySQL 通常可以隐式转换格式良好的字符串，但 STR_TO_DATE() 函数提供了更明确的控制和对不同格式的支持。
  sql
SELECT STR_TO_DATE('2023-01-15 14:30:00', '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('2023-01-15', '%Y-%m-%d');

• SQL Server:
  CAST() 和 CONVERT() 函数用于字符串到日期时间类型的转换。
  sql
SELECT CAST('2023-01-15 14:30:00' AS DATETIME);
-- 使用 CONVERT() 结合样式代码
SELECT CONVERT(DATETIME, '2023-01-15 14:30:00', 120); -- 样式 120: 'yyyy-mm-dd hh:mi:ss'
SELECT CAST('2023-01-15' AS DATE);

• Oracle:

  • TO_TIMESTAMP() 用于将字符串转换为 TIMESTAMP。
    sql
SELECT TO_TIMESTAMP('2023-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
  • TO_DATE() 用于将字符串转换为 DATE。
    sql
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD') FROM DUAL;

4. Unix 时间戳处理 (以 MySQL 为例)

Unix 时间戳是指自协调世界时（UTC）1970年1月1日00:00:00以来经过的秒数。在某些系统中，这种整数形式的时间戳很常见。

• MySQL:
  • FROM_UNIXTIME() 将 Unix 时间戳（整数）转换为 DATETIME 值。
    sql
SELECT FROM_UNIXTIME(1673790600); -- 转换为 '2023-01-15 14:30:00'
SELECT FROM_UNIXTIME(1673790600, '%Y-%m-%d'); -- 格式化输出
  • UNIX_TIMESTAMP() 将日期或 DATETIME 值转换为 Unix 时间戳。
    sql
SELECT UNIX_TIMESTAMP('2023-01-15 14:30:00'); -- 返回 1673790600
SELECT UNIX_TIMESTAMP(NOW()); -- 返回当前 Unix 时间戳

5. 时区转换

在处理跨地域或全球数据时，时区转换是不可避免的。

• PostgreSQL:
  使用 AT TIME ZONE 关键字在不同时区之间转换时间戳。
  sql
-- 将时间戳转换为 UTC 时区
SELECT timestamp_column AT TIME ZONE 'UTC' AS utc_timestamp FROM your_table;
-- 转换为 UTC 时区后，再提取日期部分
SELECT (timestamp_column AT TIME ZONE 'UTC')::DATE AS utc_date FROM your_table;

• MySQL:
  CONVERT_TZ() 函数用于进行时区转换。
  sql
-- 将 timestamp_column 从 'America/Los_Angeles' 转换为 'UTC'
SELECT CONVERT_TZ(timestamp_column, 'America/Los_Angeles', 'UTC') FROM your_table;
  注意: MySQL 的时区转换功能需要预先加载时区信息到数据库中。

• Oracle:
  FROM_TZ() 可以将 TIMESTAMP 转换为 TIMESTAMP WITH TIME ZONE 类型，从而进行时区操作。
  sql
-- 将当前系统日期转换为带有时区信息的 TIMESTAMP
SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'America/New_York') FROM DUAL;

重要注意事项

• SQL Server TIMESTAMP 数据类型: 重申，SQL Server 的 TIMESTAMP 并非日期时间类型。务必使用 DATE, TIME, DATETIME, DATETIME2 或 DATETIMEOFFSET 来存储日期和时间信息。
• 默认格式: 不同的数据库和会话设置可能有默认的日期时间格式。为了确保代码的可移植性和避免意外结果，建议在转换函数中明确指定格式。
• 精度问题: 在进行转换和格式化时，注意时间戳的精度（例如，是否包含毫秒）。不当的转换可能会导致精度丢失。
• 错误处理: 当将字符串转换为日期/时间戳时，请确保输入的字符串格式与指定的格式模式严格匹配，否则可能会导致错误或返回 NULL。在生产环境中，通常需要额外的错误处理逻辑来验证输入。

结论

SQL 中的时间戳与日期转换是数据库管理和开发中的一项基本技能。掌握各种数据库系统提供的转换函数和技巧，能够帮助您更有效地处理日期时间数据，满足各种复杂的业务需求。始终根据您使用的具体数据库系统和应用场景，选择最合适、最高效的转换方法。通过本教程的学习，希望您能对 SQL 日期时间转换有更深入的理解和实践能力。

---

Please let me know if you need any adjustments or further information!