以小时为单位的两个时间格式之间的差异

本文关键字：格式 时间 之间 两个 为单位 小时 | 更新日期: 2023-09-27 17:55:32

我在 unix 时间的数据库中有两个日期。我想要它们之间的差异，并以小时为单位显示出来。

例如
日期1： 05/07/2016 14：40：00 PM （1467733205）
日期2： 05/07/2016 15：40：05 PM （1467729600）

差异：日期2 - 日期1 = 3605 s

我想在几个小时内向他展示这个结果。 -> 1小时5秒

    SELECT TO_CHAR(to_date('01/01/1970', 'dd/mm/yyyy') + (((HOUR_DEPARTURE - DATE_LOADING )/ 60 / 60 / 24)), 'dd "days and" HH:MI:SS')
  FROM dual

我试过这样，但它需要 01 天和 01：00：05 .没有一天的区别。

以小时为单位的两个时间格式之间的差异

解决方案中的 dd 从 1970 年 1 月 1 日开始选择月份中的某一天。所以你已经从 1 天开始了。您不能简单地减去 1 来获得"0 天"，因为计算是在日历日期而不是"天数"上完成的。

如果你想在几天和hh：mi：ss内得到答案，那么你可以做这样的事情：

with
     test_data (date1, date2) as (
       select sysdate - 3605 / 86400, sysdate from dual
     )
select to_char(trunc(date2 - date1)) || ' days and ' || 
            to_char(date '1970-01-01' + (date2 - date1), 'hh24:mi:ss')
from   test_data;

TO_CHAR(TRUNC(DATE2-DATE1))||'DAYSAND'||TO_CHAR(DATE'1970-
----------------------------------------------------------
0 days and 01:00:05

Oracle Setup：

CREATE TABLE your_table ( date1, date2 ) AS
SELECT 1467733205, 1467729600 FROM DUAL;

查询：

SELECT TRUNC( diff ) AS days,
       TRUNC( MOD( diff * 24, 24 ) ) AS hours,
       TRUNC( MOD( diff * 24*60, 60 ) ) AS minutes,
       ROUND( MOD( diff * 24*60*60, 60 ) ) AS seconds
FROM   (
  SELECT ( DATE '1970-01-01' + date1 /24/60/60 )
         - ( DATE '1970-01-01' + date2 /24/60/60 ) AS diff
  FROM   your_table
);

输出：

DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
   0     1       0       5

查询 2：

SELECT TRIM(
         REGEXP_REPLACE(
           REGEXP_REPLACE(
             (  ( DATE '1970-01-01' + date1 / 24/60/60 )
              - ( DATE '1970-01-01' + date2 / 24/60/60 ) ) DAY TO SECOND,
             '^'+?(-?)0*('d+) 0?('d+):0?('d+):0?('d+)'.0+$'
             ''1'2 days '3 hours '4 minutes '5 seconds'
           ),
           '(^| )0 (days|hours|minutes|seconds)'
         )
       ) AS difference
FROM   your_table

输出：

DIFFERENCE
-----------------
1 hours 5 seconds

SELECT    DECODE (d,  0, NULL,  1, '1 day ',  TO_CHAR (d) || ' days ')
       || DECODE (h,  0, NULL,  1, '1 hour ',  TO_CHAR (h) || ' hours ')
       || DECODE (m,  0, NULL,  1, '1 minute ',  TO_CHAR (m) || ' minutes ')
       || DECODE (s,  0, NULL,  1, '1 second',  TO_CHAR (s) || ' seconds')
  FROM (SELECT EXTRACT (DAY FROM (dt)) d, EXTRACT (HOUR FROM (dt)) h, EXTRACT (MINUTE FROM (dt)) m, EXTRACT (SECOND FROM (dt)) s
          FROM (SELECT NUMTODSINTERVAL (:HOUR_DEPARTURE - :DATE_LOADING, 'DAY') AS dt FROM DUAL))

为了好玩：

SELECT    DECODE (y,  0, NULL,  1, '1 year ',  TO_CHAR (y) || ' years ') 
       || DECODE (m,  0, NULL,  1, '1 month ',  TO_CHAR (m) || ' months ') 
       || DECODE (d,  0, NULL,  1, '1 day ',  TO_CHAR (d) || ' days ')
       || DECODE (h,  0, NULL,  1, '1 hour ',  TO_CHAR (h) || ' hours ')
       || DECODE (mi,  0, NULL,  1, '1 minute ',  TO_CHAR (mi) || ' minutes ')
       || DECODE (s,  0, NULL,  1, '1 second',  TO_CHAR (s) || ' seconds')
  FROM (SELECT EXTRACT (YEAR FROM (ym)) y,EXTRACT (MONTH FROM (ym)) m, 
               EXTRACT (DAY FROM (dt)) d, EXTRACT (HOUR FROM (dt)) h, EXTRACT (MINUTE FROM (dt)) mi, EXTRACT (SECOND FROM (dt)) s
          FROM (SELECT NUMTOYMINTERVAL (MONTHS_BETWEEN (:HOUR_DEPARTURE, :DATE_LOADING), 'MONTH') AS ym ,
                       NUMTODSINTERVAL (ADD_MONTHS(:HOUR_DEPARTURE,-MONTHS_BETWEEN (:HOUR_DEPARTURE, :DATE_LOADING)) - :DATE_LOADING, 'DAY') AS dt 
                  FROM DUAL
                )
        )
相关文章：