oracle时间

Question

oracle   jhkssj VARCHAR2(5)，jhjssj VARCHAR2(5)
jhkssj       jhjssj
05:10          05:20
23:15          01:30我想得出
0.1小时
2.3小时（四舍五入）
如果太难那就
10分钟
135分钟

Answer 1

SQL> create table sj( jhkssj VARCHAR2(5),jhjssj VARCHAR2(5));
 
Table created
 
SQL> select * from sj;
 
JHKSSJ JHJSSJ
------ ------
05:10  05:20
23:15  01:30
 
SQL> --1、小时（小时应该不是10进制的吧）SQL> SELECT round(CASE
  2           WHEN jhjssj >= jhkssj THEN
  3            jhjssj - jhkssj
  4           ELSE
  5            jhjssj + 1 - jhkssj
  6         END * 24,1)
  7    FROM (SELECT to_date(to_char(trunc(SYSDATE), 'yyyy-mm-dd') || ' ' ||
  8                         jhkssj,
  9                         'yyyy-mm-dd hh24:mi') jhkssj,
 10                 to_date(to_char(trunc(SYSDATE), 'yyyy-mm-dd') || ' ' ||
 11                         jhjssj,
 12                         'yyyy-mm-dd hh24:mi') jhjssj
 13            FROM sj); 
SQL>--2、分钟 
SQL> SELECT CASE
  2           WHEN jhjssj >= jhkssj THEN
  3            jhjssj - jhkssj
  4           ELSE
  5            jhjssj + 1 - jhkssj
  6         END * 1440
  7    FROM (SELECT to_date(to_char(trunc(SYSDATE), 'yyyy-mm-dd') || ' ' ||
  8                         jhkssj,
  9                         'yyyy-mm-dd hh24:mi') jhkssj,
 10                 to_date(to_char(trunc(SYSDATE), 'yyyy-mm-dd') || ' ' ||
 11                         jhjssj,
 12                         'yyyy-mm-dd hh24:mi') jhjssj
 13            FROM sj);
 
CASEWHENJHJSSJ>=JHKSSJTHENJHJS
------------------------------
                            10
                           135
 
SQL> 

Answer 2

SQL> --1.小时
SQL> SELECT round(CASE
  2                 WHEN jhjssj >= jhkssj THEN
  3                  jhjssj - jhkssj
  4                 ELSE
  5                  jhjssj + 1 - jhkssj
  6               END * 24,
  7               1)
  8    FROM (SELECT to_date(to_char(trunc(SYSDATE), 'yyyy-mm-dd') || ' ' ||
  9                         jhkssj,
 10                         'yyyy-mm-dd hh24:mi') jhkssj,
 11                 to_date(to_char(trunc(SYSDATE), 'yyyy-mm-dd') || ' ' ||
 12                         jhjssj,
 13                         'yyyy-mm-dd hh24:mi') jhjssj
 14            FROM sj);
 
ROUND(CASEWHENJHJSSJ>=JHKSSJTH
------------------------------
                           0.2
                           2.3
 
SQL>