如何通过sql查找MYSQL表的当前日期和审计时间戳之间的最大时间戳差异? [英] How to find maximum timestamp differences by sql between the current date and audit timestamp of the MYSQL table?

问题描述

目前我有一个审计表，其中包含作业 ID、job_status_to、job_status_from 和时间戳.状态和时间戳由以下查询显示:

Currently I have an audit table that has a job id, job_status_to, job_status_from and timestamp. The statuses and timestamps are shown by the following query:

select aud.job_instance_id, aud.insert_ts, aud.job_status_from, aud.job_status_to
from job_instance_audit aud
where (job_status_from = 1 and job_status_to = 2) or (job_status_from = 2 and job_status_to = 3)
order by aud.job_instance_id desc limit 1000;

1 个 job_instance_id 的 2 行结果如下:

The results for 2 rows for one job_instance_id are as follows:

job_instance_id,           insert_ts, job_status_from, job_status_to
         371376, 2020-09-11 12:37:58,               2,             3
         371376, 2020-09-11 12:35:46,               1,             2

我可以轻松找到 job_instance_audit 表中所有行的分钟差异

I can easily find the difference in minutes for all rows in the job_instance_audit table

SELECT TIMESTAMPDIFF(minute, insert_ts,NOW()) from job_instance_audit  
where job_status_to=2 order by job_instance_id  desc limit 10000; 

但是，只有当特定作业的最大job_status_to不超过2时，我才对NOW()和表中时间戳之间的时间差感兴趣，即该作业仍处于状态2并且尚未通过到状态 3.另外，我只想查看差异大于 4 分钟的结果，从最大值开始排序.

However, I am interested in the difference of time between NOW() and the timestamp in the table only if the maximum job_status_to for a particular job does not exceed 2, i.e. the job is still in the status 2 and have not passed to the status 3. Also, I would like to see only the results with the difference greater than 4 minutes ordered by the difference starting from the maximum.

我可以通过 SQL 查询找到这种差异吗?

Can I find this difference by a SQL query?

推荐答案

根据您的描述，应该这样做:

From what you've described, something like this should do it:

SELECT job_instance_id,
       insert_ts,
       job_status_from,
       job_status_to,
       TIMESTAMPDIFF(MINUTE, insert_ts, NOW()) AS time_diff_mins
FROM   job_instance_audit
WHERE  job_status_to = 2
       AND TIMESTAMPDIFF(MINUTE, insert_ts, NOW()) > 4
       AND job_instance_id NOT IN (SELECT job_instance_id
                                   FROM   job_instance_audit
                                   WHERE  job_status_to > 2)
ORDER  BY 5 DESC 

这篇关于如何通过sql查找MYSQL表的当前日期和审计时间戳之间的最大时间戳差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！