× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. July 2000

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

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 -----
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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.