oracle 将间隔转换为分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5063214/
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
Convert interval to minutes
提问by zerkms
Let's suppose I have 2 intervals:
假设我有 2 个间隔：
INTERVAL '0 00:30:00' DAY TO SECOND
INTERVAL '0 04:00:00' DAY TO SECOND
What is the most elegant way to get amount of minutes in each interval. 30and 240accordingly.
在每个间隔中获取分钟数的最优雅方法是什么。30并240相应地。
Yes, I know I can perform EXTRACT(HOUR FROM interval) * 60 + EXTRACT(MINUTE FROM interval), but this looks terrible to me.
是的，我知道我可以表演EXTRACT(HOUR FROM interval) * 60 + EXTRACT(MINUTE FROM interval)，但这对我来说看起来很糟糕。
Any better solutions?
有什么更好的解决方案吗？
回答by Rob van Wijk
What looks terrible to you, looks perfectly acceptable to me. If you look at the documentation at the arithmetic you can perform on INTERVALs:
对你来说看起来很糟糕的东西，对我来说却是完全可以接受的。如果您查看算术文档，您可以对 INTERVAL 执行：
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref175
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref175
then you see you can multiply them with numerics. So if you multiply your intervals to 24 and 60, you can get the number of minutes by extracting the number of days. It's more compact, but I doubt if it's more elegant in your view.
然后你看到你可以用数字乘以它们。因此，如果将间隔乘以 24 和 60，则可以通过提取天数来获得分钟数。它更紧凑，但我怀疑在您看来它是否更优雅。
SQL> create table t (my_interval interval day to second)
2 /
Table created.
SQL> insert into t
2 select numtodsinterval(30,'minute') from dual union all
3 select numtodsinterval(4,'hour') from dual
4 /
2 rows created.
SQL> select my_interval
2 , 60 * extract(hour from my_interval)
3 + extract(minute from my_interval) minutes_terrible_way
4 , extract(day from 24*60*my_interval) minutes_other_way
5 from t
6 /
MY_INTERVAL MINUTES_TERRIBLE_WAY MINUTES_OTHER_WAY
------------------------------ -------------------- -----------------
+00 00:30:00.000000 30 30
+00 04:00:00.000000 240 240
2 rows selected.
Regards,
Rob.
问候，
罗布。
回答by Splash
(extract(day from my_interval) * 24 + extract(hour from my_interval)) * 60 + extract(minute from my_interval) my_way
Don't forget about days.
不要忘记几天。
回答by indhar
Reusing Rob's example above,
重用 Rob 上面的例子，
select (sysdate-(sysdate-to_dsinterval(my_interval)))*24*60 from t;
