Oracle：如何减去两个日期并获得结果的分钟数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13408819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle : how to subtract two dates and get minutes of the result
提问by Frank
I wrote this function to get minutes from a date, but I cannot get minutes between two dates, How to get that ?
我编写了这个函数来获取某个日期的分钟数，但我无法获取两个日期之间的分钟数，如何获取？
FUNCTION get_minute(p_date DATE)
RETURN NUMBER
IS
BEGIN
IF p_date IS NOT NULL THEN
return EXTRACT(MINUTE FROM TO_TIMESTAMP(to_char(p_date,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH24:MI:SS'));
ELSE
RETURN 0;
END IF;
END get_minute;
回答by lc.
When you subtract two dates in Oracle, you get the number of days between the two values. So you just have to multiply to get the result in minutes instead:
当您在 Oracle 中减去两个日期时，您会得到两个值之间的天数。所以你只需要乘以几分钟就能得到结果：
SELECT (date2 - date1) * 24 * 60 AS minutesBetween
FROM ...
回答by KeyMaker00
For those who want to substrat two timestamps (instead of dates), there is a similar solution:
对于那些想要减去两个时间戳（而不是日期）的人，有一个类似的解决方案：
SELECT ( CAST( date2 AS DATE ) - CAST( date1 AS DATE ) ) * 1440 AS minutesInBetween
FROM ...
or
或者
SELECT ( CAST( date2 AS DATE ) - CAST( date1 AS DATE ) ) * 86400 AS secondsInBetween
FROM ...
回答by ibrahim
I can handle this way:
我可以这样处理：
select to_number(to_char(sysdate,'MI')) - to_number(to_char(*YOUR_DATA_VALUE*,'MI')),max(exp_time) from ...
Or if you want to the hour just change the MI;
或者，如果您想要小时，只需更改 MI；
select to_number(to_char(sysdate,'HH24')) - to_number(to_char(*YOUR_DATA_VALUE*,'HH24')),max(exp_time) from ...
the others don't work for me. Good luck.
其他人对我不起作用。祝你好运。
回答by Raúl Moreno
I think you can adapt the function to substract the two timestamps:
我认为您可以调整该函数以减去两个时间戳：
return EXTRACT(MINUTE FROM
TO_TIMESTAMP(to_char(p_date1,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
-
TO_TIMESTAMP(to_char(p_date2,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
);
I think you could simplify it by just using CAST(p_date as TIMESTAMP).
我认为您可以通过使用CAST(p_date as TIMESTAMP).
return EXTRACT(MINUTE FROM cast(p_date1 as TIMESTAMP) - cast(p_date2 as TIMESTAMP));
Remember dates and timestamps are big ugly numbers inside Oracle, not what we see in the screen; we don't need to tell him how to read them. Also remember timestamps can have a timezone defined; not in this case.
请记住，日期和时间戳在 Oracle 中是非常丑陋的数字，而不是我们在屏幕上看到的；我们不需要告诉他如何阅读它们。还要记住时间戳可以定义一个时区；在这种情况下不是。
