如何将MariaDB中created_at时间戳转换为距当前的总小时数？

在MariaDB 10.5.12中，直接使用TIMESTAMPDIFF()函数就能简便计算created_at到当前时间的总小时数，无需复杂逻辑。

修改后的查询语句 #

SELECT 
    hostname, 
    uuid, 
    instances.created_at, 
    instances.deleted_at, 
    JSON_EXTRACT(flavor, '$.cur.*."name"') AS FLAVOR,
    TIMESTAMPDIFF(HOUR, instances.created_at, NOW()) AS hours_since_created
FROM instances 
JOIN instance_extra ON instances.uuid = instance_extra.instance_uuid 
WHERE (vm_state='active' OR vm_state='stopped');

示例输出 #

+----------+--------------------------------------+---------------------+------------+--------------+---------------------+
| hostname | uuid                                 | created_at          | deleted_at | FLAVOR       | hours_since_created |
+----------+--------------------------------------+---------------------+------------+--------------+---------------------+
| vm1      | ef6380b4-5455-48f8-9e4b-3d04199be3f5 | 2023-01-05 14:25:51 | NULL       | ["tempest2"] | 12345               |
+----------+--------------------------------------+---------------------+------------+--------------+---------------------+
1 row in set (0.001 sec)

函数说明 #

• TIMESTAMPDIFF(unit, start_time, end_time)：返回end_time与start_time的时间差，第一个参数指定时间单位，这里用HOUR表示小时。
• NOW()：获取当前系统时间，作为时间差计算的结束节点。

如果存在created_at为NULL的记录，可通过IFNULL处理避免返回空值：

IFNULL(TIMESTAMPDIFF(HOUR, instances.created_at, NOW()), 0) AS hours_since_created

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