| convert secconds to day:hour:minute:seconds format [message #274151] |
Sun, 14 October 2007 13:17  |
NIckman
Messages: 64
Registered: May 2007
|
Member |
|
|
Hi
i used this function for time stamp
cast
( numtodsinterval(test_TimeStamp4 - test_TimeStamp2, 'DAY')
as interval day(2) to second(0)
)
which is working very fine.
I want sum of seconds column. I mean
the column name is test_cputime which is Number(200)
add all columns and convert into day:hour:min:sec format.
so i use this function.
cast
( numtodsinterval(sum(test_cputime), 'DAY')
as interval day(2) to second(0)
)
it is not working. why?.
pl help.
|
|
|
|
| Re: convert secconds to day:hour:minute:seconds format [message #274152 is a reply to message #274151] |
Sun, 14 October 2007 13:40  |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Why don't you want to follow the rules?
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).
In addition, this works for me:
SQL> with data as (select level test_cputime from dual connect by level <= 3)
2 select cast
3 ( numtodsinterval(sum(test_cputime), 'DAY')
4 as interval day(2) to second(0)
5 )
6 from data
7 /
CAST(NUMTODSINTERVAL(SUM(TEST_CPUTIME),'DAY')ASINTERVALDAY(2)TOSECOND(0))
---------------------------------------------------------------------------
+06 00:00:00
1 row selected.
Regards
Michel
[Updated on: Sun, 14 October 2007 13:40] Report message to a moderator |
|
|
|
| Re: convert secconds to day:hour:minute:seconds format [message #274946 is a reply to message #274152] |
Wed, 17 October 2007 17:16 |
NIckman
Messages: 64
Registered: May 2007
|
Member |
|
|
Michael,
thanks for your reply . sorry about that.
But still it is not working. i got this.
My query
| Quote: |
select test_dst||','||
cast
( numtodsinterval(sum(test_cputime), 'DAY')
as interval day(2) to second(0)
)||','||
sum(test_count)
from tab1,tab2
where tab1_nbr = tab2_nbr
and test_dst='PP55'
and test_TimeStamp3 >= TO_DATE('&backdate# 00:59:00','YYYY-MM-DD HH24:MI:SS')
and test_TimeStamp4 <= TO_DATE('&frontdate# 23:59:59','YYYY-MM-DD HH24:MI:SS')
GROUP BY test_dst;
|
i got like this
ORA-01877: string is too long for internal buffer.
cast
( numtodsinterval(sum(test_cputime), 'DAY')
as interval day(2) to second(0)
am i missing any spaces or commas.
let me know
thanks,N
|
|
|
|
|
|
|
|
| Re: convert secconds to day:hour:minute:seconds format [message #275212 is a reply to message #274151] |
Thu, 18 October 2007 16:56 |
NIckman
Messages: 64
Registered: May 2007
|
Member |
|
|
I used different fucntion this time. I got this error.
| Quote: |
select test_dst||','||
to_char(to_date(sum(ah_cputime),'sssss'),'hh24:mi:ss')||','||
sum(test_count)
from tab1,tab2
where tab1_nbr = tab2_nbr
and test_dst='PP55'
and test_TimeStamp3 >= TO_DATE('&backdate# 00:59:00','YYYY-MM-DD HH24:MI:SS')
and test_TimeStamp4 <= TO_DATE('&frontdate# 23:59:59','YYYY-MM-DD HH24:MI:SS')
GROUP BY test_dst;
|
*
ORA-01830: date format picture ends before converting entire input string
pl help.
where is wrong in that query?
thx.N.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: convert secconds to day:hour:minute:seconds format [message #275217 is a reply to message #274151] |
Thu, 18 October 2007 17:14 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>then why you are replying at the beginning?
To give you a clue so you can fix YOUR problem.
I am sorry that you are displeased with my response.
You are entitled to a 100% FULL refund.
Why do you expect others to solve your problems for you; quickly & for free?
I promise I won't disappoint you in the future, because you seem to prefer no response to one containing a clue.
[Updated on: Thu, 18 October 2007 19:00] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: convert secconds to day:hour:minute:seconds format [message #354493 is a reply to message #274151] |
Sun, 19 October 2008 22:23 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I am not much for timestamps and maybe I am not paying close enough attention to the post, but for dates, maybe the following snippet will help you:
SQL> alter session set nls_date_format = 'dd-mon-rrrr hh24:mi:ss';
Session altered.
SQL>
SQL>
SQL> select 60 minute,60*60 hour,60*60*24 day,60*60*24*7 week from dual
2 /
MINUTE HOUR DAY WEEK
---------- ---------- ---------- ----------
60 3600 86400 604800
SQL> select to_date('1/1/0001','mm/dd/rrrr') from dual
2 /
TO_DATE('1/1/0001','
--------------------
01-jan-0001 00:00:00
SQL>
SQL> select to_date('1/1/0001','mm/dd/rrrr')+604800/24/60/60 from dual
2 /
TO_DATE('1/1/0001','
--------------------
08-jan-0001 00:00:00
SQL> select to_date('1/1/0001','mm/dd/rrrr')+604800/24/60/60-1 from dual
2 /
TO_DATE('1/1/0001','
--------------------
07-jan-0001 00:00:00
SQL>
SQL> select to_char(to_date('1/1/0001','mm/dd/rrrr')+604800/24/60/60-1,'DDD:hh24:mi:ss') from dual
2 /
TO_CHAR(TO_D
------------
007:00:00:00
SQL>
Good luck, Kevin
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
