variance in mins

Posted in 2008

Summary

The poster wanted the difference between two Informix date/datetime columns expressed as a whole number of minutes rather than an interval. Mike Aubury suggested casting the subtraction to an INTERVAL MINUTE(n) TO MINUTE (or adding INTERVAL(0) MINUTE(9) TO MINUTE and using EXTEND), and noted that a column alias can't be reused in a CASE in the same SELECT (use a temp table, or repeat the expression, comparing against e.g. '60 UNITS MINUTE'). Since the appointment was stored as separate DATE and TIME columns, Carsten Haese supplied a small UDR to combine them: return extend(d, year to second) + (t - "00:00:00").

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

vchu via DBMonster.com — Thu, 17 Apr 2008 16:56:08 GMT — source: Usenet: comp.databases.informix

i have to date fields in informix anyone knows how to get the variance in mins or second pls? select (Act_DT - Appt_DT) from contract will return somelike like 0 01:21 -- h mm:ss i like to get 81 mins for this case (1*60 + 21) = 81 mins Thanks Victor -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200804/1

↪ replying to vchu via DBMonster.com

Mike Aubury — Thu, 17 Apr 2008 18:16:57 +0100 — source: Usenet: comp.databases.informix

dependant on version - something like : select (Act_DT - Appt_DT)::interval minute(5) to minute from contract ? On Thursday 17 April 2008 17:56:08 vchu via DBMonster.com wrote: > i have to date fields in informix > > anyone knows how to get the variance in mins or second pls? > > select (Act_DT - Appt_DT) from contract > > will return somelike like 0 01:21 -- h mm:ss > > i like to get 81 mins for this case (1*60 + 21) = 81 mins > > Thanks > Victor

↪ replying to Mike Aubury

vchu via DBMonster.com — Thu, 17 Apr 2008 18:26:43 GMT — source: Usenet: comp.databases.informix

I tried this , it works select interval(0) minute(9) to minute + (extend(s.act_arrival_dt, year to minute) - extend(s.appt_nlt_date, year to minute)) as VarianceInMins, case when VarianceInMins<=0 then "On Time/Early" when VarianceinMins>60 then "Late > 1 hr" when VarianceinMins>=15 and VarianceinMins<=60 then "Late 15mins - 1 hr" when VarianceinMins>0 and VarianceinMins<15 then "Late 15mins - 1 hr" when VarianceinMins<=0 then "On time < 15mins" else "" end AS Performance, but it is not working on the case, it is looking for the field "VarianceInMins" any idea?? Mike Aubury wrote: >dependant on version - something like : > >select (Act_DT - Appt_DT)::interval minute(5) to minute >from contract > >? > >> i have to date fields in informix >> >[quoted text clipped - 8 lines] >> Thanks >> Victor -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200804/1

↪ replying to vchu via DBMonster.com

Mike Aubury — Thu, 17 Apr 2008 20:15:41 +0100 — source: Usenet: comp.databases.informix

I dont think you can use a column alias like that unless you put it into a temp table first.. Also - there seems to be a lot of casting/arithmetic in - so shouldnt that just be: case when s.act_arrival_dt-s.appt_nlt_date<=0 THEN "On time early" when s.act_arrival_dt-s.appt_nlt_date>60 units minutes THEN "Over an hour" .. etc (assuming act_arrival_dt and appt_nlt_date are both datetimes) On Thursday 17 April 2008 19:26:43 vchu via DBMonster.com wrote: > I tried this , it works > > select interval(0) minute(9) to minute + (extend(s.act_arrival_dt, year to > minute) - extend(s.appt_nlt_date, year to minute)) as VarianceInMins, > > case > when VarianceInMins<=0 then "On Time/Early" > when VarianceinMins>60 then "Late > 1 hr" > when VarianceinMins>=15 and VarianceinMins<=60 then "Late > 15mins - 1 hr" > when VarianceinMins>0 and VarianceinMins<15 then "Late 15mins > - 1 hr" > when VarianceinMins<=0 then "On time < 15mins" > else "" > end AS Performance, > > > but it is not working on the case, it is looking for the field > "VarianceInMins" > > any idea?? > > Mike Aubury wrote: > >dependant on version - something like : > > > >select (Act_DT - Appt_DT)::interval minute(5) to minute > >from contract > > > >? > > > >> i have to date fields in informix > > > >[quoted text clipped - 8 lines] > > > >> Thanks > >> Victor

↪ replying to Mike Aubury

vchu via DBMonster.com — Thu, 17 Apr 2008 19:48:00 GMT — source: Usenet: comp.databases.informix

appt_nlt_date is date field only :( Mike Aubury wrote: >I dont think you can use a column alias like that unless you put it into a >temp table first.. > >Also - there seems to be a lot of casting/arithmetic in - so shouldnt that >just be: > > case > when s.act_arrival_dt-s.appt_nlt_date<=0 THEN "On time early" > when s.act_arrival_dt-s.appt_nlt_date>60 units minutes THEN "Over an hour" >.. >etc > >(assuming act_arrival_dt and appt_nlt_date are both datetimes) > >> I tried this , it works >> >[quoted text clipped - 30 lines] >> >> Thanks >> >> Victor -- Message posted via http://www.dbmonster.com

↪ replying to vchu via DBMonster.com

Mike Aubury — Thu, 17 Apr 2008 20:51:01 +0100 — source: Usenet: comp.databases.informix

So - just cast (or extend) it - and it'll be a datetime :-) I'm not sure you're maths makes much sense though if you're worried about hours and minutes - when one of your operands is measured in days! On Thursday 17 April 2008 20:48:00 vchu via DBMonster.com wrote: > appt_nlt_date is date field only :( > > Mike Aubury wrote: > >I dont think you can use a column alias like that unless you put it into a > >temp table first.. > > > >Also - there seems to be a lot of casting/arithmetic in - so shouldnt that > >just be: > > > > case > > when s.act_arrival_dt-s.appt_nlt_date<=0 THEN "On time early" > > when s.act_arrival_dt-s.appt_nlt_date>60 units minutes THEN "Over an > > hour" .. > >etc > > > >(assuming act_arrival_dt and appt_nlt_date are both datetimes) > > > >> I tried this , it works > > > >[quoted text clipped - 30 lines] > > > >> >> Thanks > >> >> Victor

↪ replying to Mike Aubury

vchu via DBMonster.com — Thu, 17 Apr 2008 20:12:52 GMT — source: Usenet: comp.databases.informix

Actually I need to select s.act_arrival_dt - s.appt_nlt_date + s.appt_nlt_time from contract s.act_arrival_dt is datetime field s.appt_nlt_date is date only s.appt_nlt_time is time only any advice? Mike Aubury wrote: >So - just cast (or extend) it - and it'll be a datetime :-) > >I'm not sure you're maths makes much sense though if you're worried about >hours and minutes - when one of your operands is measured in days! > >> appt_nlt_date is date field only :( >> >[quoted text clipped - 18 lines] >> >> >> Thanks >> >> >> Victor -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200804/1

↪ replying to vchu via DBMonster.com

Carsten Haese — Thu, 17 Apr 2008 23:08:57 GMT — source: Usenet: comp.databases.informix

vchu via DBMonster.com wrote: > Actually I need to > > select s.act_arrival_dt - s.appt_nlt_date + s.appt_nlt_time from contract > > s.act_arrival_dt is datetime field > s.appt_nlt_date is date only > s.appt_nlt_time is time only > > any advice? 1) Shoot the person that designed the table structure. 2) Use the following function to combine separate date and time fields to a single datetime field: create function combine_date_time(d date, t datetime hour to second) returning datetime year to second; return (extend(d, year to second) + (t-"00:00:00")); end function; HTH, -- Carsten Haese http://informixdb.sourceforge.net