This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / Oracle SQL 问题：如何把一个interval类型转换成一个number类型，见内一张表里又两个timestamp with time zone的列，想写一个query，get count(*) from the table, group by interval of the two columns. the problem is, i want the interval to be rounded (or trunced) to the day.
this is the start point:
select (a - b) as name, count(*) as cnt from this_table group by (a-b);
thank you.
-bloor(Shall We Dance?);
2004-10-19
{318}
(#1937287@0)
-
?
-bloor(Shall We Dance?);
2004-10-19
(#1937329@0)
-
oracle的手都哪去了，帮帮我呀
-bloor(Shall We Dance?);
2004-10-19
(#1937559@0)
-
Try trunc (not sure, not using Oracle for quite a while)
-artshen(夏昏);
2004-10-19
(#1937568@0)
-
nope, i tried, doesn't work
-bloor(Shall We Dance?);
2004-10-19
(#1937570@0)
-
round ( date, [ format ] ) ?
-guestagain(guest again);
2004-10-19
(#1937655@0)
-
when i substract two date value, the result is interval data type, not a date date type. so the round function won't work here.
-bloor(Shall We Dance?);
2004-10-19
(#1937719@0)
-
try this!SELECT COUNT(*), extract( day from (a-b) ) interDays
FROM this _table GROUP BY (a-b);
-changshan(偷偷乐);
2004-10-19
{88}
(#1937753@0)
-
bingo, it works. thank you so much.
-bloor(Shall We Dance?);
2004-10-19
(#1937776@0)