如何在Oracle SQL Developer中计算最新下载时间戳与当前时间戳差值

根据你使用的数据库类型，可通过以下SQL语句直接计算最新下载时间戳与当前时间的差值：

Oracle（适配你用到的dual表场景） #

Oracle中TIMESTAMP类型直接相减会得到INTERVAL DAY TO SECOND格式的差值，也可提取具体时间分量：

SELECT 
  CURRENT_TIMESTAMP - MAX(srvr_timestamp) AS 时间差,
  EXTRACT(DAY FROM CURRENT_TIMESTAMP - MAX(srvr_timestamp)) AS 天数差,
  EXTRACT(HOUR FROM CURRENT_TIMESTAMP - MAX(srvr_timestamp)) AS 小时差,
  EXTRACT(MINUTE FROM CURRENT_TIMESTAMP - MAX(srvr_timestamp)) AS 分钟差,
  EXTRACT(SECOND FROM CURRENT_TIMESTAMP - MAX(srvr_timestamp)) AS 秒数差
FROM jaherna42.download;

直接相减的结果格式类似+00 03:15:45.789，表示0天3小时15分45.789秒的间隔。

MySQL #

使用TIMESTAMPDIFF函数指定返回的时间单位：

SELECT 
  TIMESTAMPDIFF(SECOND, MAX(srvr_timestamp), CURRENT_TIMESTAMP) AS 秒数差,
  TIMESTAMPDIFF(MINUTE, MAX(srvr_timestamp), CURRENT_TIMESTAMP) AS 分钟差,
  TIMESTAMPDIFF(HOUR, MAX(srvr_timestamp), CURRENT_TIMESTAMP) AS 小时差,
  TIMESTAMPDIFF(DAY, MAX(srvr_timestamp), CURRENT_TIMESTAMP) AS 天数差
FROM jaherna42.download;

第一个参数可替换为WEEK、MONTH等其他时间单位。

SQL Server #

使用DATEDIFF函数计算差值：

SELECT 
  DATEDIFF(SECOND, MAX(srvr_timestamp), GETDATE()) AS 秒数差,
  DATEDIFF(MINUTE, MAX(srvr_timestamp), GETDATE()) AS 分钟差,
  DATEDIFF(HOUR, MAX(srvr_timestamp), GETDATE()) AS 小时差,
  DATEDIFF(DAY, MAX(srvr_timestamp), GETDATE()) AS 天数差
FROM jaherna42.download;

若需要更高精度的当前时间，可替换GETDATE()为SYSDATETIME()。

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