PostgreSQL timestamp查询转MS SQL/Azure SQL的实现方法咨询

直接等价查询 #

和你提供的PostgreSQL逻辑完全对齐的Azure SQL写法如下：

SELECT count(*) 
FROM info_event 
WHERE event_time < DATEDIFF_BIG(SECOND, '1970-01-01 00:00:00', DATEADD(MONTH, -1, GETUTCDATE())) * 1000;

对应逻辑说明：

• DATEADD(MONTH, -1, GETUTCDATE()) 对应PostgreSQL的current_timestamp - INTERVAL '1 MONTH'，表示取当前UTC时间往前推1个月的时间点；如果你的业务使用本地时间存储时间戳，可以将GETUTCDATE()替换为GETDATE()。
• DATEDIFF_BIG(SECOND, '1970-01-01 00:00:00', 目标时间) 对应PostgreSQL的EXTRACT(epoch FROM 目标时间)，计算目标时间和Unix纪元起始时间的秒差，返回bigint类型，无需额外类型转换。
• 末尾乘1000是将秒级时间戳转换为毫秒级，和你表中存储的bigint类型毫秒时间戳匹配。

更简洁的等价写法 #

你也可以直接计算毫秒差，省去乘法步骤，逻辑完全等价：

SELECT count(*) 
FROM info_event 
WHERE event_time < DATEDIFF_BIG(MILLISECOND, '1970-01-01 00:00:00', DATEADD(MONTH, -1, GETUTCDATE()));

清理脚本建议 #

如果要实际删除过期数据，推荐用分页删除的方式，避免一次性删除大量数据导致锁表、事务日志暴涨：

WHILE 1=1
BEGIN
    DELETE TOP (1000) 
    FROM info_event 
    WHERE event_time < DATEDIFF_BIG(MILLISECOND, '1970-01-01 00:00:00', DATEADD(MONTH, -1, GETUTCDATE()));
    IF @@ROWCOUNT < 1000 BREAK;
END

Epoch时间通用处理规则 #

Azure SQL/SQL Server中处理Unix时间戳的通用逻辑如下：

• 时间转Epoch秒级戳：DATEDIFF_BIG(SECOND, '1970-01-01 00:00:00', 待转换时间)
• 时间转Epoch毫秒级戳：DATEDIFF_BIG(MILLISECOND, '1970-01-01 00:00:00', 待转换时间)
• Epoch秒级戳转时间：DATEADD(SECOND, 秒级戳数值, '1970-01-01 00:00:00')
• Epoch毫秒级戳转时间：DATEADD(MILLISECOND, 毫秒级戳数值, '1970-01-01 00:00:00')
• 时间间隔计算统一使用DATEADD函数，第一个参数传时间单位，第二个传偏移值（负数为往前推），第三个传基准时间

注意：必须使用DATEDIFF_BIG而非普通DATEDIFF，后者返回int类型，2038年之后会出现溢出问题，和表中bigint类型的时间戳也更匹配

内容的提问来源于stack exchange，提问作者guyfromnowhere