最近公司的项目要从mysql迁到人大金仓,其中遇到了一个小时差的问题
MySQL 的天和小时的计算方式
DATEDIFF( CURRENT_DATE (), expiration_time ) //计算相差几天
TIMESTAMPDIFF( HOUR, expiration_time, NOW())//计算相差几小时
人大金仓只兼容DATEDIFF,但是TIMESTAMPDIFF计算的结果不正确,
运用时间转时间戳
SELECT EXTRACT(epoch FROM cast('2024-01-01 00:00:00'AS timestamp ) );
SELECT EXTRACT(epoch FROM now());
具体应用,时间戳除以3600得到小时差,完美解决
round((EXTRACT(epoch FROM now())-EXTRACT(epoch FROM cast(expiration_time AS timestamp)))/60/60