Home » SQL & PL/SQL » SQL & PL/SQL » convert secconds to day:hour:minute:seconds format
convert secconds to day:hour:minute:seconds format [message #274151] Sun, 14 October 2007 13:17 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #274948 is a reply to message #274151] Wed, 17 October 2007 17:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
01877, 00000, "string is too long for internal buffer"
// *Cause: This is an internal error.
// *Action: Please contact Oracle Worldwide Support.
Re: convert secconds to day:hour:minute:seconds format [message #274997 is a reply to message #274946] Thu, 18 October 2007 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to give:
- Oracle version with 4 decimals
- test case that we can reproduce

Regards
Michel
Re: convert secconds to day:hour:minute:seconds format [message #275212 is a reply to message #274151] Thu, 18 October 2007 16:56 Go to previous messageGo to next message
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 #275213 is a reply to message #274151] Thu, 18 October 2007 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>where is wrong in that query?
ORA-01830: date format picture ends before converting entire input string
SQL*Plus will point to the part of the code it reports as error.
Most likely the TO_CHAR needs to be fixed.

[Updated on: Thu, 18 October 2007 16:59] by Moderator

Report message to a moderator

Re: convert secconds to day:hour:minute:seconds format [message #275214 is a reply to message #274151] Thu, 18 October 2007 17:05 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
can you tell me correct query.
thx.N.
Re: convert secconds to day:hour:minute:seconds format [message #275215 is a reply to message #274151] Thu, 18 October 2007 17:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>can you tell me correct query.
NO
Re: convert secconds to day:hour:minute:seconds format [message #275216 is a reply to message #274151] Thu, 18 October 2007 17:07 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
then why you are replying at the beginning?
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 #275260 is a reply to message #275212] Fri, 19 October 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 18 October 2007 08:24

You have to give:
- Oracle version with 4 decimals
- test case that we can reproduce

Regards
Michel


Re: convert secconds to day:hour:minute:seconds format [message #354438 is a reply to message #275217] Sat, 18 October 2008 23:18 Go to previous messageGo to next message
bunch1962
Messages: 1
Registered: October 2008
Junior Member
Cheese dude - why be a total dick to someone who is asking for help?
Re: convert secconds to day:hour:minute:seconds format [message #354440 is a reply to message #354438] Sun, 19 October 2008 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bunch1962 wrote on Sun, 19 October 2008 06:18
Cheese dude - why be a total dick to someone who is asking for help?

Why doesn't he want to post the information we ask him in order to help him?
Can you understand and explain that?

Regards
Michel

[Updated on: Sun, 19 October 2008 00:12]

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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: convert secconds to day:hour:minute:seconds format [message #354526 is a reply to message #354493] Mon, 20 October 2008 03:20 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of the last why this and why that - well, why are you arguing over a year old discussion?
Previous Topic: stored procedure
Next Topic: how to list selected field in whole database
Goto Forum:
  


Current Time: Thu Dec 08 14:02:05 CST 2016

Total time taken to generate the page: 0.13862 seconds