Home » SQL & PL/SQL » SQL & PL/SQL » convert time part into second (oracle 10g r2)
convert time part into second [message #584249] Fri, 10 May 2013 09:27 Go to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
Dear All,

How can i convert the time part into second. for example

in mu table i have below rows

1753-01-01 09:18:00.000
1753-01-01 09:52:00.000
1753-01-01 09:36:00.000

I want to convert the time part of each rows to number of second in different column. so for the 1st rows it is 09:18:00.000 that means 9hour and 18 min = 9*60*60+18*60.
But that is complex solumn. any thinng easy that this
Re: convert time part into second [message #584250 is a reply to message #584249] Fri, 10 May 2013 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 22897
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: convert time part into second [message #584252 is a reply to message #584250] Fri, 10 May 2013 09:45 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
The difference between the column and the column without the time element (you can use the TRUNC function for that) will return the fraction of a day. Multiply that by 24*60*60 to get the number of seconds.
Re: convert time part into second [message #584255 is a reply to message #584252] Fri, 10 May 2013 10:55 Go to previous messageGo to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
I KHOW this but any easy way to do this like if i can multiply 1/86400 to get the number of second
Re: convert time part into second [message #584257 is a reply to message #584255] Fri, 10 May 2013 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Was the input column a string or a date?

From your previous topic:

Michel Cadot wrote on Thu, 21 March 2013 14:32
From your previous topics:

Michel Cadot wrote on Wed, 13 February 2013 16:56
...
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...


Michel Cadot wrote on Thu, 14 February 2013 00:49
...
Waiting for the test case... (which should take you 1 minute).
...


And feedback to those that help you or ask something; see your previous topics.


You do not deserve to be helped more.

Regards
Michel

[Updated on: Fri, 10 May 2013 11:20]

Report message to a moderator

Re: convert time part into second [message #584261 is a reply to message #584249] Fri, 10 May 2013 11:38 Go to previous messageGo to next message
smohanh
Messages: 1
Registered: May 2013
Location: Chennai
Junior Member

SELECT SYSDATE, TO_CHAR (SYSDATE, 'J') * 86400 + TO_CHAR (SYSDATE, 'SSSSS') sec
FROM DUAL;


Output:

SYSDATE SEC
--------- ---
10-MAY-13 212235007064

Thanks,
Shankar
Re: convert time part into second [message #584263 is a reply to message #584255] Fri, 10 May 2013 12:47 Go to previous messageGo to next message
joy_division
Messages: 4527
Registered: February 2005
Location: East Coast USA
Senior Member
guddu_12 wrote on Fri, 10 May 2013 11:55
I KHOW this but any easy way to do this like if i can multiply 1/86400 to get the number of second


Easy? What is wrong with pablolee's answer? That is as easy as it gets.
Re: convert time part into second [message #584264 is a reply to message #584261] Fri, 10 May 2013 12:50 Go to previous messageGo to next message
joy_division
Messages: 4527
Registered: February 2005
Location: East Coast USA
Senior Member
smohanh wrote on Fri, 10 May 2013 12:38
SELECT SYSDATE, TO_CHAR (SYSDATE, 'J') * 86400 + TO_CHAR (SYSDATE, 'SSSSS') sec
FROM DUAL;


Not sure where you're going with this, but it does not answer the OP question.
Re: convert time part into second [message #584265 is a reply to message #584264] Fri, 10 May 2013 13:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
joy_division wrote on Fri, 10 May 2013 13:50
Not sure where you're going with this, but it does not answer the OP question.


Oh, I am sure it is what op wants (unless op also wants fraction of a second, although it is 0 in test case):

select  9*60*60+18*60 sec1,
        to_char(to_timestamp('1753-01-01 09:18:00.000','yyyy-mm-dd hh24:mi:ss.ff'),'sssss') sec2
  from  dual
/

      SEC1 SEC2
---------- -----
     33480 33480

SQL> 


SY.
Re: convert time part into second [message #584266 is a reply to message #584264] Fri, 10 May 2013 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But almost... given OP's behaviour I will not say more.

Regards
Michel
Re: convert time part into second [message #584274 is a reply to message #584265] Fri, 10 May 2013 15:25 Go to previous messageGo to next message
joy_division
Messages: 4527
Registered: February 2005
Location: East Coast USA
Senior Member
Solomon Yakobson wrote on Fri, 10 May 2013 14:17
joy_division wrote on Fri, 10 May 2013 13:50
Not sure where you're going with this, but it does not answer the OP question.


Oh, I am sure it is what op wants (unless op also wants fraction of a second, although it is 0 in test case):


Yes Solomon, your example shows the correct 33480 seconds, but smohanh's example gave an answer that is either wrong, or for some reason I cannot understand.
Re: convert time part into second [message #584280 is a reply to message #584274] Fri, 10 May 2013 18:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
joy_division wrote on Fri, 10 May 2013 16:25
but smohanh's example gave an answer that is either wrong, or for some reason I cannot understand.


smohanh decided to convert whole date to seconds using December 31, 4713 BC as epoch date. 86400 is number of seconds in a day.

SY.

[Updated on: Fri, 10 May 2013 18:46]

Report message to a moderator

Re: convert time part into second [message #584393 is a reply to message #584280] Mon, 13 May 2013 11:08 Go to previous messageGo to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
Hi Sorry for the delay

my table is below
CREATE TABLE EXMP
(
  INCIDENTDATE         DATE,
  INCIDENTTIME         DATE,
  RESTIME              DATE,
  REMIND_TO_COMPLETE   DATE,
  INITIALDELAYMINUTES  NUMBER,
  INCIDENTTYPE         VARCHAR2(250 BYTE)
)


data
SET DEFINE OFF;
Insert into EXMP
   (INCIDENTDATE, INCIDENTTIME, RESTIME, REMIND_TO_COMPLETE, INITIALDELAYMINUTES, 
    INCIDENTTYPE)
 Values
   (TO_DATE('05/07/2013 14:44:34', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/1753 14:44:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/1753 00:01:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/08/2013 01:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 
    'FLS');
Insert into EXMP
   (INCIDENTDATE, INCIDENTTIME, RESTIME, REMIND_TO_COMPLETE, INITIALDELAYMINUTES, 
    INCIDENTTYPE)
 Values
   (TO_DATE('05/08/2013 14:15:51', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/1753 13:15:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/08/2013 14:16:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/09/2013 01:00:00', 'MM/DD/YYYY HH24:MI:SS'), 60, 
    'TWD');
Insert into EXMP
   (INCIDENTDATE, INCIDENTTIME, RESTIME, REMIND_TO_COMPLETE, INITIALDELAYMINUTES, 
    INCIDENTTYPE)
 Values
   (TO_DATE('05/09/2013 11:02:36', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/1753 11:02:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/1753 00:01:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/10/2013 01:00:00', 'MM/DD/YYYY HH24:MI:SS'), 50, 
    'SIG');
COMMIT;


I want to add timepart of restime column and initialdealy to incidentdate but i get wrong figure as my below calculation add to date instead of time
TO_NUMBER(TO_CHAR (RESTIME ,'HH24'))*3600 + TO_NUMBER(TO_CHAR(RESTIME ,'MI'))*60 + TO_NUMBER(TO_CHAR( RESTIME ,'SS')) + INITIALDELAYMINUTES *60.

so from the data i have incidentdate = 08/05/2013 14:15:51 and i add restime and inditialdelayminute after conveting it to sec it gives me '51360'.
this figure is being added to incidentdate as a whole instead of its time component

Michal sorry for the delay as for some reason i couldn't do it

Re: convert time part into second [message #584394 is a reply to message #584393] Mon, 13 May 2013 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INCIDENTDATE        INCIDENTTIME        RESTIME             REMIND_TO_COMPLETE  INITIALDELAYMINUTES INCIDENTTYPE
------------------- ------------------- ------------------- ------------------- ------------------- ------------
07/05/2013 14:44:34 01/01/1753 14:44:00 01/01/1753 00:01:00 08/05/2013 01:00:00                  10 FLS
08/05/2013 14:15:51 01/01/1753 13:15:00 08/05/2013 14:16:00 09/05/2013 01:00:00                  60 TWD
09/05/2013 11:02:36 01/01/1753 11:02:00 01/01/1753 00:01:00 10/05/2013 01:00:00                  50 SIG

So what is the result you want from the data you gave?

Regards
Michel
Re: convert time part into second [message #584425 is a reply to message #584393] Tue, 14 May 2013 05:26 Go to previous message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
guddu_12 wrote on Mon, 13 May 2013 12:08
I want to add timepart of restime column and initialdealy to incidentdate


Oracle date arithmetic uses day as unit of measure. So, RESTIME - TRUNC(RESTIME) is RESTIME time portion expressed in days (e.g. 12 hours is 0.5, 1 hour is 1 / 24, etc.). So now you should understand how to deal with INITIALDELAYMINUTES. To convert it to days all you need is INITIALDELAYMINUTES / 60 / 24. And to summarize:

INCIDENTDATE + (RESTIME - TRUNC(RESTIME)) + INITIALDELAYMINUTES / 60 / 24 


SY.
Previous Topic: SQL Logic
Next Topic: SQL Update statement for OLTP table
Goto Forum:
  


Current Time: Mon Oct 20 04:16:04 CDT 2014

Total time taken to generate the page: 0.12398 seconds