SQL时间戳函数参考
主流数据库系统时间戳操作的快速参考。
获取当前时间戳
| 数据库 | 当前时间戳 (UTC) | 当前时间戳 (本地) |
|---|---|---|
| MySQL | NOW(), UTC_TIMESTAMP() | NOW(), LOCALTIMESTAMP() |
| PostgreSQL | NOW(), CURRENT_TIMESTAMP | NOW(), CURRENT_TIMESTAMP |
| SQLite | datetime('now') | datetime('now', 'localtime') |
| SQL Server | GETUTCDATE() | GETDATE(), SYSDATETIME() |
| Oracle | SYSTIMESTAMP, SYS_EXTRACT_UTC(SYSTIMESTAMP) | SYSTIMESTAMP |
Unix时间戳操作
获取当前Unix时间戳
| 数据库 | 代码 |
|---|---|
| MySQL | UNIX_TIMESTAMP() |
| PostgreSQL | EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) |
| SQLite | strftime('%s', 'now') |
| SQL Server | DATEDIFF(s, '1970-01-01', GETUTCDATE()) |
| Oracle | (SYSTIMESTAMP - TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD')) * 86400 |
Unix时间戳转日期
| 数据库 | 代码 |
|---|---|
| MySQL | FROM_UNIXTIME(1704067200) |
| PostgreSQL | TO_TIMESTAMP(1704067200) |
| SQLite | datetime(1704067200, 'unixepoch') |
| SQL Server | DATEADD(s, 1704067200, '1970-01-01') |
| Oracle | TO_DATE('1970-01-01', 'YYYY-MM-DD') + (1704067200 / 86400) |
日期转Unix时间戳
| 数据库 | 代码 |
|---|---|
| MySQL | UNIX_TIMESTAMP('2024-01-01 00:00:00') |
| PostgreSQL | EXTRACT(EPOCH FROM '2024-01-01 00:00:00'::timestamp) |
| SQLite | strftime('%s', '2024-01-01 00:00:00') |
| SQL Server | DATEDIFF(s, '1970-01-01', '2024-01-01 00:00:00') |
| Oracle | (TO_DATE('2024-01-01', 'J') - TO_DATE('1970-01-01', 'J')) * 86400 |
日期格式化
常用日期格式
| 数据库 | ISO 8601 | 美国格式 | 欧洲格式 |
|---|---|---|---|
| MySQL | DATE_FORMAT(NOW(), '%Y-%m-%dT%H:%i:%s') | DATE_FORMAT(NOW(), '%m/%d/%Y') | DATE_FORMAT(NOW(), '%d/%m/%Y') |
| PostgreSQL | TO_CHAR(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS') | TO_CHAR(NOW(), 'MM/DD/YYYY') | TO_CHAR(NOW(), 'DD/MM/YYYY') |
| SQLite | datetime('now', 'iso8601') | - | - |
| SQL Server | FORMAT(GETUTCDATE(), 'yyyy-MM-ddTHH:mm:ss') | FORMAT(GETDATE(), 'MM/dd/yyyy') | FORMAT(GETDATE(), 'dd/MM/yyyy') |
| Oracle | TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS') | TO_CHAR(SYSDATE, 'MM/DD/YYYY') | TO_CHAR(SYSDATE, 'DD/MM/YYYY') |
时区操作
获取当前时区
| 数据库 | 代码 |
|---|---|
| MySQL | TIMEZONE_NAME, @@time_zone |
| PostgreSQL | current_setting('timezone') |
| SQLite | 不支持时区 |
| SQL Server | GETDATE(), DATENAME(TIMEZONE, SYSDATETIMEOFFSET()) |
| Oracle | SESSIONTIMEZONE, DBTIMEZONE |
时区转换
| 数据库 | 代码 |
|---|---|
| MySQL | CONVERT_TZ('2024-01-01 12:00:00', 'UTC', 'Asia/Shanghai') |
| PostgreSQL | '2024-01-01 12:00:00 UTC'::timestamptz AT TIME ZONE 'Asia/Shanghai' |
| SQLite | 不支持时区 |
| SQL Server | GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'China Standard Time' |
| Oracle | FROM_TZ(TIMESTAMP '2024-01-01 12:00:00', 'UTC') AT TIME ZONE 'Asia/Shanghai' |
时间戳计算
加减时间
| 数据库 | 加1天 | 减1小时 |
|---|---|---|
| MySQL | DATE_ADD(NOW(), INTERVAL 1 DAY) | DATE_SUB(NOW(), INTERVAL 1 HOUR) |
| PostgreSQL | NOW() + INTERVAL '1 day' | NOW() - INTERVAL '1 hour' |
| SQLite | datetime('now', '+1 day') | datetime('now', '-1 hour') |
| SQL Server | DATEADD(day, 1, GETDATE()) | DATEADD(hour, -1, GETDATE()) |
| Oracle | SYSDATE + 1 | SYSDATE - (1/24) |
计算差值
| 数据库 | 天数差 |
|---|---|
| MySQL | DATEDIFF('2024-01-15', '2024-01-01') |
| PostgreSQL | '2024-01-15'::date - '2024-01-01'::date |
| SQLite | julianday('2024-01-15') - julianday('2024-01-01') |
| SQL Server | DATEDIFF(day, '2024-01-01', '2024-01-15') |
| Oracle | TO_DATE('2024-01-15') - TO_DATE('2024-01-01') |
最佳实践
1. 使用UTC存储时间戳
始终以UTC格式存储时间戳，只在显示给用户时才转换为本地时间。
SQL1-- 推荐：使用UTC存储 2CREATE TABLE events ( 3 id INT, 4 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 5); 6 7-- 更好：明确指定UTC 8CREATE TABLE events ( 9 id INT, 10 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 11);
2. 使用适当的数据类型
| 使用场景 | 推荐类型 |
|---|---|
| UTC存储 | TIMESTAMP WITH TIME ZONE |
| 本地时间 | TIMESTAMP WITHOUT TIME zone |
| Unix时间戳 | BIGINT（便于移植） |
| 仅日期 | DATE |
3. 正确处理时区转换
SQL1-- PostgreSQL示例 2SELECT 3 created_at AT TIME ZONE 'UTC' AS utc_time, 4 created_at AT TIME ZONE 'Asia/Shanghai' AS shanghai_time 5FROM events;