Home » SQL & PL/SQL » SQL & PL/SQL » dislaying a number in timestamp format
dislaying a number in timestamp format [message #213085] Tue, 09 January 2007 07:49 Go to next message
sjanuzi
Messages: 3
Registered: January 2007
Location: Brazil
Junior Member

I have a proxy account table that has a column with users connection time in miliseconds like this:

user1 34120 ms
user2 3440 ms
... and so on.

how to make a select to display the connection time in timestamp format converting the number of miliseconds in HH:MI:SS?

user1 01:20:34 (hh:mi:ss)?

Is it possible to get this format using a simple select statement ?
My database is oracle 8.1.7.4 in W2k.

thanks for advance.



Re: dislaying a number in timestamp format [message #213131 is a reply to message #213085] Tue, 09 January 2007 11:00 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
I am sure that there must be a easy way of doing this. But right now I am not able to find it.

If the miliseconds are - 222020 then It is 1Hr,1Min and 40 secs

You can replace the figure "222020" with your figure.

Try this. And if you get any easier way please do post it.

select
222020 miliseconds,
floor(222000/216000),
floor(mod(222000,216000)/3600) mins,
222000/60 - 60*(floor(mod(222000,216000)/3600) +floor(222000/216000)*60) secs,
to_char(floor(222000/216000),'00')||':'||to_char(floor(mod(222000,216000)/3600),'00')||to_char(
222000/60 - 60*(floor(mod(222000,216000)/3600) +floor(222000/216000)*60),'00') Display_time
from
dual;

Regards,
Anil
Re: dislaying a number in timestamp format [message #213151 is a reply to message #213085] Tue, 09 January 2007 11:55 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
try

select TO_CHAR(trunc(sysdate) + (connection_time / 86400000),'HH24:MI.SS') TIME_STAMP
from my_table;

[Updated on: Tue, 09 January 2007 11:56]

Report message to a moderator

Re: dislaying a number in timestamp format [message #213360 is a reply to message #213131] Wed, 10 January 2007 07:37 Go to previous messageGo to next message
sjanuzi
Messages: 3
Registered: January 2007
Location: Brazil
Junior Member

Anil,
thanks for your help but it seems that something is wrong on your query :

222020 miliseconds are: 222 seconds at total ==> 3 minutes and 42 seconds.

I made some changes in your query and it seems to work fine.
Try this:
SELECT P1.MILISECONDS,
P1.HOURS,
P1.MINUTES,
P1.SECONDS,
lpad(P1.HOURS,2,'0')||':'||lpad(P1.MINUTES,2,'0')||':'||
lpad(P1.SECONDS,2,'0') "DURATION"
FROM
(select
222020 miliseconds,
222020/1000 TOTSECONDS,
floor(222020/1000/3600) HOURS,
FLOOR(mod(222020/1000,3600)/60) MINUTES,
floor(mod(mod(222020/1000,3600),60)) SECONDS from dual) P1
/

Regards
Solange
Re: dislaying a number in timestamp format [message #213375 is a reply to message #213151] Wed, 10 January 2007 08:30 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Bill B wrote on Tue, 09 January 2007 11:55
try

select TO_CHAR(trunc(sysdate) + (connection_time / 86400000),'HH24:MI.SS') TIME_STAMP
from my_table;


Try running

select TO_CHAR(trunc(sysdate) + (222020 / 86400000),'HH24:MI.SS') TIME_STAMP
from dual;

You will get the same result as the much more complicated query above.
Re: dislaying a number in timestamp format [message #213377 is a reply to message #213375] Wed, 10 January 2007 08:46 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Try this -

select
222020 miliseconds,
floor(222020/3600000) hrs,
floor(222020/1000) secs,
floor(222020/60000) mins,
to_char(floor(222020/3600000),'00')||':'||to_char(floor(222020/60000)-floor(222020/3600000)*60,'00')||':'||to_char(floor(222020/1000) -(floor(222020/60000)*60),'00') Display_time
from
dual;



3905000 miliseconds is 1 hr 5 mins and 5 secs

select
3905000 miliseconds,
floor(3905000/3600000) hrs,
floor(3905000/1000) secs,
floor(3905000/60000) mins,
to_char(floor(3905000/3600000),'00')||':'||to_char(floor(3905000/60000)-floor(3905000/3600000)*60,'00')||':'||to_char(floor(3905000/1 000)-(floor(3905000/60000)*60),'00') Display_time
from
dual;

Regards,
Anil
Re: dislaying a number in timestamp format [message #213380 is a reply to message #213377] Wed, 10 January 2007 09:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think @Bill B's solution is neater, easier to maintain, and more comprehensible.
Re: dislaying a number in timestamp format [message #213391 is a reply to message #213375] Wed, 10 January 2007 09:50 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Thanks Bill for a good query.
Re: dislaying a number in timestamp format [message #213399 is a reply to message #213375] Wed, 10 January 2007 10:56 Go to previous message
sjanuzi
Messages: 3
Registered: January 2007
Location: Brazil
Junior Member

Bill, I am surprised for the simplicity and objectivity of your query!
That is everything I need!

Thanks
Regards
Solange
Previous Topic: Regards Locking of Objects
Next Topic: how 0 can be replaced
Goto Forum:
  


Current Time: Thu Dec 08 04:18:05 CST 2016

Total time taken to generate the page: 0.05252 seconds