Interval between time start and end

[marcohadiyanto]

Hi All,

i'm trying to get interval between time start and time end (StartTime) - (EndTime), my time format is 24 hours. when StartTime greater than EndTime, it's fine, but  if EndTime greater than StartTime, the interval function always doing the minimum interval.

ex 1:

StartTime : 10:00:00

EndTime : 12:00:00

interval : 2:00

---------------------------------> it's work perfect it's calculate between 10AM to 12 PM.

ex2:

StartTime : 10:00:00

EndTime : 02:00:00

interval 8:00

--------------------------------->it's Error because it's only calculate between 10AM to 2AM. the result i want is 16:00

what's expression to make this interval correctly?

regards,,

Marco

[CELAMBARASAN]

Hi,

     You have AM and PM notation in that time period?

     I mean

StartTime : 10:00:00 AM

EndTime : 02:00:00 PM

Celambarasan

[marcohadiyanto]

Hi,

i dont have AM and PM notation, but the time format already 24hour.

Regards,

[CELAMBARASAN]

Hi,

     It has only time?No date before it?

     because as per your case 10:00:00 is on the day '03/05/2012' (March 05) means then '02:00:00' willbe on March 06.am i right?

Celambarasan

[jagan]

Hi,

If the Time1 and Time2 are times of different dates then there should date should also be there in the field.  Then only it is possible.

By using Interval() the difference is calculated.

Regards,

Jagan.

[perumal_41]

Hi,

Try This Method

if( EndTime<StartTime,Time('24:00:00','h:mm:ss ')-(EndTime-StartTime),EndTime-StartTime)

Regards

Perumal

[marcohadiyanto]

"Hi,

     It has only time?No date before it?

     because as per your case 10:00:00 is on the day '03/05/2012' (March 05) means then '02:00:00' willbe on March 06.am i right?

Celambarasan"

Hi, yes you right. it's like that. i dont have the date value only time. but my time it's can't over 24 hours. the max betweek start and end is 24 Hours.

Regards,

[jagan]

Hi,

Check this example

=interval('2012-03-02 10:00:00'- '2012-03-01 02:00:00 ', 'hh:mm' )

Regards,

Jagan.

[marcohadiyanto]

hi Jagan,

i dont have date value, it's only time value. if i have date value then it don't have problem with this..

any other to make it happen?

Regards,

Marco

[jagan]

Hi,

If you don't have the date, then how would you know that both dates has difference of 1day or 2days.

Check whether you have Start and End date in any other columns, if you have then we can append the date and time and do this calculation.

Can you post the sample data.

Regards,

Jagan.