IDS Forum

Re: subtract datetime value from null

Posted By: Jonathan Leffler
Date: Friday, 31 August 2012, at 10:30 a.m.

In Response To: subtract datetime value from null (PRAJAKTA CHIKHALE)

On Fri, Aug 31, 2012 at 6:05 AM, PRAJAKTA CHIKHALE <prajakta_s_c@yahoo.co.in
> wrote:

> Hello I have a table in which there are two fields actual_time_from,
> actual_time_to. It stores sign on and sign off details. Null is inserted if
> actual time to extends past 23:59.
>
> How do I find hours worked if actual time to is null.
>
> I need to do something like
> select actual_time_to - actual_time_from as work_hours from t1;
>
> If I try using nvl(actual_time_to, to_date('24:00', '%H:%M')) it gives
> error
> -1263 A field in a datetime or interval is out of range, incorrect, or
> missing.
>
> If I try using nvl(actual_time_to, to_date('01 00:00', '%d %H:%M')) for
> fields
> where it is not null; 31 is put in day field. This leads to incorrect
> subtraction when actual_time_from is not null and actual_time_to is null.
>
> -30 06:00 is the output in such case when instead I want it to output 18:00
> when actual_time_from is "06:00" and actual_time_to is null.
>
> If I put nvl(actual_time_to, to_date('24:00', '%H:%M')), difference
> evaluates
> to 17:59 in previous case which is not correct logically.
>
> Things work only if both are null. But in that case, I can leave it blank.
>

Typo for 'if both are not null'?

> I can do the calculation by taking it in java but for 1 specific report I
> need
> to happen through sql may be through temporary tables but using sql only.
>

With addendum:

Sorry the line with output calculated as 17:59 should have read
> nvl(actual_time_to, to_date('23:59', '%H:%M'))
>

It may not be obvious (it probably isn't obvious), but it is often easiest
to do time calculations when the type is INTERVAL rather than DATETIME HOUR
TO MINUTE (in this case, DATETIME HOUR TO SECOND, etc).

In this case, I'd probably look to convert the times by subtracting
DATETIME(00:00) HOUR TO MINUTE from the times you've got stored. Note, in
particular, that INTERVAL(24:00) HOUR TO MINUTE is a very respectable value.

To calculate the end time:

NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR
TO MINUTE)

To calculate the start time:

(actual_time_from - DATETIME(00:00) HOUR TO MINUTE)

So the time taken is:

NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE, INTERVAL(24:00) HOUR
TO MINUTE) -
(actual_time_from - DATETIME(00:00) HOUR TO MINUTE)

Which is a mouthful, but effective. Test case:

CREATE TABLE WorkTimes
(

actual_time_from DATETIME HOUR TO MINUTE NOT NULL,

actual_time_to DATETIME HOUR TO MINUTE
);
INSERT INTO WorkTimes VALUES('09:00', '17:00');
INSERT INTO WorkTimes VALUES('13:00', NULL);
SELECT Actual_time_to, actual_time_from,

(actual_time_from - DATETIME(00:00) HOUR TO MINUTE) fr_interval,

NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE,
INTERVAL(24:00) HOUR TO MINUTE) AS to_interval,

NVL(actual_time_to - DATETIME(00:00) HOUR TO MINUTE,
INTERVAL(24:00) HOUR TO MINUTE) -

(actual_time_from - DATETIME(00:00) HOUR TO MINUTE) AS time_worked
FROM WorkTimes
ORDER BY actual_time_from;

17:00 09:00 9:00 17:00 8:00

13:00 13:00 24:00 11:00

The output looks better in fixed-width font, of course.

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--f46d04083aa12feb7604c890a39f

Messages In This Thread

• subtract datetime value from null
  PRAJAKTA CHIKHALE -- Friday, 31 August 2012, at 9:05 a.m.
  • Re: subtract datetime value from null
    PRAJAKTA CHIKHALE -- Friday, 31 August 2012, at 9:06 a.m.
  • Re: subtract datetime value from null
    Jonathan Leffler -- Friday, 31 August 2012, at 10:30 a.m.
    • Re: subtract datetime value from null
      PRAJAKTA CHIKHALE -- Saturday, 1 September 2012, at 12:55 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.