Convert Systime to seconds

Hi,

I need to do a calculation in my Select statement. The calculation is
((((1 + sysdate() - "job-dt-start") * 86400) +
         systime() - "job-tm-start" - 86400) / 86400)

in the above my job-tm-start is the time, its in  integer and stored as seconds.
but the systime is in HH:MM:SS format.  how to convert it to seconds.

Thanks
sarorenAsked:
Who is Participating?
 
cobi100Commented:
well I'm not sure what you're trying to accomplish, but to convert the systime into seconds, I think you can use second, minute and hour, so try this:


select  "job-currop" ,"job-dt-start", "job-tm-start", systime,
((((1 + sysdate() - "job-dt-start") * 86400) +
     (hour(systime())*3600+minute(systime())*60+second(systime())) - "job-tm-start" - 86400) / 86400)
0
 
TNameCommented:
Do you mean something like this (get the system time and multiply it's hour and minute component corespondingly)?:

procedure TForm1.Button1Click(Sender: TObject);
var
SysTime:TSystemTime;
sec:Integer;
begin
   GetLocalTime(sysTime);
   sec:=sysTime.wHour*3600+sysTime.wMinute*60+sysTime.wSecond;
   ShowMessage(IntToStr(sec));
end;
0
 
sarorenAuthor Commented:
No, I should be able to use it in the SQL statement.

select  "job-currop" ,"job-dt-start", "job-tm-start", systime,
((((1 + sysdate() - "job-dt-start") * 86400) +
     systime() - "job-tm-start" - 86400) / 86400)

from pub.job  where "act-ship" is null  

I am not able to do the above since the Systime is in the format  of "HH:MM:SS" and my job-tm-start has time stored in seconds (integer).


Thanks
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
sarorenAuthor Commented:
Any help Please!!!!
0
 
2266180Commented:
well that is not  adelphi quesiton but an sql question.
the following should help though I am not exactly sure if extract is supported in every DB server:

select  "job-currop" ,"job-dt-start", "job-tm-start", systime,
((((1 + sysdate() - "job-dt-start") * 86400) +
     extract (hour from systime())*3600+extract(minute from systime())*60+extract(second from systime()) - "job-tm-start" - 86400) / 86400)
0
 
ewildeCommented:

which SQL server are  you  connect to?
what do you mean by stored as integer? do you mean that 1AM=3600?
in general, schematicly, maybe you should do:

cast(substring(systime,1,2) as integer)*3600
+ cast(substring(systime,4,2) as integer)*60
+ cast(substring(systime,7,2) as integer)


i'm not sure i anderstand your question...
regards,
ewilde.
0
 
sarorenAuthor Commented:
I am using Progress Database

Hi Ciuly,

If i use the extract command it give me syntax error. So i guess its not supported.  is there any other way.
But thanks for your help

Hi Ewilde,

The current time is stored in a database field. the field is a integer format.
eg 12:45:13 is stored as 45913
 
you are right 01:00:00 AM is stored as 3600

I tried using your syntax it give an error "Inconsistent types"

so i just tried substring(systime,1,2)  still i have the same error.

Thanks
0
 
sarorenAuthor Commented:
Thanks cobi100,

Your solution worked.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.