Difference between times 1

[VDavid]

Hi,
I want to get the difference between two times, the data is like this:

Id StarTime EndTime
1 11:55:27.945 null
2 11:55:34.765 12:55:57.766
3 12:56:19.047 14:59:29.703
4 00:03:30.842 null
5 02:20:44.165 15:08:32.579
6 15:25:58.247 16:35:13.895

what i want is to get a 4 column TOTAL like:

Id TOTAL
1 (blank , because EndTime is null)
2 01:00
3 02:03
4 (blank , because EndTime is null)
5 12:48
6 01:10

I'm using Sybase SQL Anywhere 5.5
Columns StarTime and EndTime are TIME

Is there a way of doing this in a single SELECT.

Any help will be appreciate.

Thanks,
David.

P.S. I'm new in Tek-Tips, should I post this here or in another forum.

 

[grega]

You could try using the datediff function (if it's available in SQL Anywhere).

select id, datediff(ms,end,start)

Greg.

 

[VDavid]

Greg
I did use it, but the output is not in hh:mm format, I also tried to do the calculations myself to format the output but I always get some extra or less minutes than it should be and the performance also decrease when i put to many conversion and operations in the select statmente.

Thanks Greg, if you have another idea, or how to do it with datediff that gives an exect result, I will really apreciate. I really really really (did I mention relly?) need this.

David.

 

[JeanNiBee]

Hi

Use the datediff() and then convert() the result.

Ex.

SELECT CONVERT( CHAR(10), datediff( result_you_want, date1, date2) , 108 )

Style codes are as folllows:
(this is not an exhaustive list).
8 or 108 -> hh:mi:ss
14 or 114 -> hh:mi:ss:mmm

Hope this helps.

-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[VDavid]

Thanks for the reply JeanNiBee,
But the

datediff( result_you_want, date1, date2)

function always return an integer value not a time value, not matter what i use in resul_you_want, maybe i'm doing something wrong.
The convert styles codes are usefull if you have a time value, but as i explain above the result is an integer.

Any idea what i'm doing wrong?,
David.

 

[JeanNiBee]

Sorry missed the part where the time is an integer.

I'll look into this for you.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

Hi

Well first off thank my colleague Ashok for helping me figure out how to convert an int into a date.

But, this may not work in all cases, we have ot assume (As per your structures) that all times are based on the same day as you only store the time portion of the datetime.

Here's what I used.

CREATE TABLE TestingTimes( 
   time1 varchar(20),
   time2 varchar(20) 
)

INSERT INTO TestingTimes values ( "11:15:40.30", "14:47:40.35" )
INSERT INTO TestingTimes values ( "11:15:40.30", "13:41:29.35" )

Lastly, to get your Elapsed times, I use the following. (And I'll explain it after the examples).

SELECT time1 "Starting Time", time2 "Ending Time", 
    CONVERT (  
        VARCHAR(11), 
        DATEADD ( ss, 
            DATEDIFF( 
                ss, 
                CONVERT( datetime, time1, 100), 
                CONVERT( datetime, time2, 100) 
            ),
            "Jan 01, 1970"
        ),
        108
    ) "Elapsed Time"
FROM TestingTimes

First off we convert your times into datetime format. Since the times are assumed to be on the dame say, we base them on the 'epoch' Jan 1, 1970. This happens by default when ypou create a datetime that is missing the 'date' part.
ss, CONVERT( datetime, time1, 100), CONVERT( datetime, time2, 100)


Next we find the difference between these two times in seconds. Again, since all tims were based on the same day, then, the elapsed time cannot be larger than 23:59.59 in length, so we add these seconds to the epoch Jan 1, 1970, also giving us a valid date to perform out conversion.
DATEADD( ss, DATEDIFF( "AS PASTED ABOVE" ), "Jan 01, 1970&quot

Then you can convert this datetime stamp into style 108 which gives you the formatting you needed

Hope this helps.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[VDavid]

JeanNiBee,
Thanks a lot for the reply, it works perfect.

David.