asked on

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

TName

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;

saroren

ASKER

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

saroren

ASKER

Any help Please!!!!

2266180

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)

ewilde

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.

saroren

ASKER

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

ASKER CERTIFIED SOLUTION

cobi100

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

saroren

ASKER

Thanks cobi100,

Your solution worked.