在Oracle中麻烦日期减少 [英] Trouble with date substraction in Oracle
问题描述
我有一个查询:
select
status,
id,
to_char(creationdatetime,'yyyy / mm / dd hh24:mm:ss')as Creation_Time,
to_char (lastmodificationdatetime,'yyyy / mm / dd hh24:mm:ss')as Last_Mod_Time,
substr(lastmodificationdatetime - creationdatetime,1,30)* 24作为Time_Between,
--trunc(((((86400 *(lastmodificationdatetime - creationdatetime))/ 60)/ 60)/ 24)Days,
--trunc(((86400 *(lastmodificationdatetime - creationdatetime))/ 60)/ 60)-24 * (((86400 *(lastmodificationdatetime - creationdatetime))/ 60)/ 60)/() (((86400 *(lastmodificationdatetime - creationdatetime))/ 60)/ 60))Min,
--trunc(86400 *(lastmodificationdatetime - creationdatetime)) - 60 *(trunc(((86400 *(lastmodificationdatetime- creationDatetime))/ 60))Sec
从
表
其中
Status ='Com 状态=已取消;
(trunc是检查其他计数日期的方式)
然后我得到结果:
状态ID创建日期Lastmodificationdate Time_Between天数分钟秒数
完成id1 2013/03/25 12:03:14 2013/03/25 13:03:17 1,784166666666666666666666648 0 1 47 3
完成id2 2013/03/26 09:03:22 2013/03 / 26 09:03:28 0,451666666666666666666666656 0 0 27 5
已取消is3 2012/12/19 17:12:50 2012/12/19 19:12:10 1,52222222222222222222222222208 0 1 31 19
取消id4 2012/12/19 18:12:13 2012/12/19 19:12:23 0,65277777777777777777777777768 0 0 39 10
我们可以看到日期被错误地删除,当我复制日期并使用双重减法:
select(to_date('2013/03/25 13:03:17','yyyy / MM / dd HH24:MI:SS') -
to_date('2013/03/25 12:03:14' ,'yyyy / MM / dd HH24:MI:SS'))
from dual;
我得到正确的结果...我不知道发生了什么...
感谢任何帮助。
您正在显示格式不正确的日期。
mm
是月,而不是分钟。所以你在时间显示中重复一个月。
您想要:'yyyy / mm / dd hh24:mi:ss'
(注意 mi
在格式掩码中)
i have some trouble doing dates substractions on Oracle database.
I have a query:
select
status,
id,
to_char(creationdatetime,'yyyy/mm/dd hh24:mm:ss') as Creation_Time,
to_char(lastmodificationdatetime,'yyyy/mm/dd hh24:mm:ss') as Last_Mod_Time,
substr(lastmodificationdatetime - creationdatetime,1,30)*24 as Time_Between,
--trunc((((86400*(lastmodificationdatetime - creationdatetime))/60)/60)/24) "Days",
--trunc(((86400*(lastmodificationdatetime - creationdatetime))/60)/60)-24*(trunc((((86400*(lastmodificationdatetime - creationdatetime))/60)/60)/24)) "Hrs",
--trunc((86400*(lastmodificationdatetime - creationdatetime))/60)-60*(trunc(((86400*(lastmodificationdatetime - creationdatetime))/60)/60)) "Min",
--trunc(86400*(lastmodificationdatetime - creationdatetime))-60*(trunc((86400*(lastmodificationdatetime - creationdatetime))/60)) "Sec"
from
table
where
Status='Completed' or Status='Cancelled';
(trunc is to check other way of counting date)
Then I get results:
Status ID CreationDate Lastmodificationdate Time_Between Days Hours Minutes Seconds
Completed id1 2013/03/25 12:03:14 2013/03/25 13:03:17 1,78416666666666666666666666648 0 1 47 3
Completed id2 2013/03/26 09:03:22 2013/03/26 09:03:28 0,45166666666666666666666666656 0 0 27 5
Cancelled is3 2012/12/19 17:12:50 2012/12/19 19:12:10 1,52222222222222222222222222208 0 1 31 19
Cancelled id4 2012/12/19 18:12:13 2012/12/19 19:12:23 0,65277777777777777777777777768 0 0 39 10
As we can see dates are substracted wrongly, when i copy dates and substracted them using dual:
select (to_date('2013/03/25 13:03:17', 'yyyy/MM/dd HH24:MI:SS') -
to_date('2013/03/25 12:03:14', 'yyyy/MM/dd HH24:MI:SS'))
from dual;
I get correct results... I have no idea whats going on... Would appreciate any help.
You are displaying the dates with an incorrect format.
mm
is the month, not minutes. So you are repeating the month in the time display.
You want: 'yyyy/mm/dd hh24:mi:ss'
(note the mi
in the format mask)
这篇关于在Oracle中麻烦日期减少的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!