PLSQL - 如何减去时间戳类型的时间（hh:mi）

发布于 2024-12-27 03:00:47 字数 204 浏览 5 评论 0原文

我有两个带有时间戳类型的字段，time_in 和 time_out。
例如：
time_out = 02-MAY-11 07.30.00.000000 PM 和
time_in = 02-MAY-11 07.57.00.000000 AM

我想从时间戳字段中获取不同的小时数。
如何做到这一点？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风向决定发型 2025-01-03 03:00:47

这是您要找的吗？ Extract 也适用于间隔（这是两个时间戳之间直接相减所得到的结果）。

create table times (time_in timestamp, time_out timestamp);
insert into times values(to_timestamp('02-MAY-11 07.57.00.000000 AM'), to_timestamp('02-MAY-11 07.30.00.000000 PM'));
select extract(hour from(time_out-time_in))||':'||extract(minute from(time_out-time_in)) from times;

EXTRACT(HOURFROM(TIME_OUT-TIME_IN))||':'||EXTRACT(MINUTEFROM(TIME_OUT-TIME_IN))   
--------------------------------------------------------------------------------- 
11:33

Is this what you're looking for? Extract also works on intervals (which is what a direct subtraction between two timestamps gives you).

create table times (time_in timestamp, time_out timestamp);
insert into times values(to_timestamp('02-MAY-11 07.57.00.000000 AM'), to_timestamp('02-MAY-11 07.30.00.000000 PM'));
select extract(hour from(time_out-time_in))||':'||extract(minute from(time_out-time_in)) from times;

EXTRACT(HOURFROM(TIME_OUT-TIME_IN))||':'||EXTRACT(MINUTEFROM(TIME_OUT-TIME_IN))   
--------------------------------------------------------------------------------- 
11:33

回复收藏 0 原文

ぺ禁宫浮华殁 2025-01-03 03:00:47

请参阅 Extract 函数

从上面：

SQL>
SQL>
SQL> --EXTRACT(): get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP():
SQL>
SQL> SELECT
  2    EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2005 19:15:26',
  3      'DD-MON-YYYY HH24:MI:SS')) AS HOUR,
  4    EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2005 19:15:26',
  5      'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,
  6    EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2005 19:15:26',
  7      'DD-MON-YYYY HH24:MI:SS')) AS SECOND
  8  FROM dual;

      HOUR     MINUTE     SECOND
---------- ---------- ----------
        19         15         26

SQL>

See the Extract function

From the above:

SQL>
SQL>
SQL> --EXTRACT(): get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP():
SQL>
SQL> SELECT
  2    EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2005 19:15:26',
  3      'DD-MON-YYYY HH24:MI:SS')) AS HOUR,
  4    EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2005 19:15:26',
  5      'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,
  6    EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2005 19:15:26',
  7      'DD-MON-YYYY HH24:MI:SS')) AS SECOND
  8  FROM dual;

      HOUR     MINUTE     SECOND
---------- ---------- ----------
        19         15         26

SQL>

回复收藏 0 原文

~没有更多了~