Re: Query........Manipulating TimeFields

["John Taylor" <john.taylor@xxxxxxxxxxxxxxx>]

Query huh... I was afraid you were going to say that. <g>

 

I think someone else already identified a solution using Query/400 (Charly Jones?), but I can't find the message in the Midrange archives, so some of this might be repetitive of what he's already shown you.

 

In a nutshell, what you want to do is the following:

 

In the "Define result fields" section:

 

- Create a TIMESTAMP field for each date/time that will be used in the calculations.

- Create a duration field to hold the result of the calculation.

- Parse the duration field to format the duration value for your report.

 

Example:

 

Assuming that a character date in CCYYMMDD format is stored within the database, you would create a timestamp value as follows:

 

(First parse the character date, and format to "yyyy-mm-dd")

 

Field                    Expression

BEGDATE            Substr(MyDate,1,4) || '-' ||

                            Substr(MyDate,5,2) || '-' ||

                            Substr(MyDate,7,2)

 

( Now convert numeric time to character type and format to "hh.mm.ss"

 

NUMTIME            Digits(MyTime)

 

BEGTIME            Substr(NumTime,1,2) || '.' ||

                            Substr(NumTime,3,2) || '.' ||

                            Substr(NumTime,5,2)

 

( Now convert your character date/time values into a timestamp )

 

BEGVALUE          TimeStamp(BegDate, BegTime)

 

 

Repeat the process for your "End" time value, and calculate the duration as follows:

 

DURATION            EndValue - BegValue

 

 

"Duration" will now contain the elapsed time in a "timestamp duration" format. This format is "yyyymmddhhMMsszzzzzz", where the "yyyy" portion represents the number of years between periods, "mm" the number of months between periods, "dd" days, "hh" hours, and so on.

 

You can extract various parts of this format using the functions "YEAR", "MONTH", "DAY", "HOUR", "MINUTE", "SECOND" and "MICROSECOND". This will allow you to create your own output formats. If you're more specific about how you'd like the final result to appear, we may be able to help.

 

 

Regards,

 

John Taylor

Canada

 

----- Original Message -----

From: sgt.walsh@icdc.com

To: MIDRANGE-L@midrange.com

Sent: Friday, July 14, 2000 12:05

Subject: Re: Query........Manipulating TimeFields

Yes, I will be calculating elapsed times across date boundaries.
I will be doing this in Query 400.

Sergeant John P. Walsh
Haverford Township Police Department
1010 Darby Road
Havertown, PA 19083
Voice   610.853.1298 ext 212
Fax      610.853.9210
sgt_walsh@icdc.com