MariaDB convert created_at timestamp in total hours from now-CodePudding

I have following question which return created_at timestamps. I would like to convert it in total hours from now. Is there an easy way to make that conversion and print it in total hours?

MariaDB version 10.5.12-MariaDB-1:10.5.12 maria~focal-log

MariaDB [nova]> select hostname, uuid, instances.created_at, instances.deleted_at, json_extract(flavor, '$.cur.*."name"') AS FLAVOR 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       |
 ---------- -------------------------------------- --------------------- ------------ -------------- 
| vm1      | ef6380b4-5455-48f8-9e4b-3d04199be3f5 | 2023-01-05 14:25:51 | NULL       | ["tempest2"] |
 ---------- -------------------------------------- --------------------- ------------ -------------- 
1 row in set (0.001 sec)

CodePudding user response：

Try it like this:

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

Demo fiddle