Re: Query vs. time
>Have a problem over which I've been slaving for two days. I obviuosly need
>assistance!
>
>First:
>Informix 7.11-UC1 on Solaris 2.5
>
>I need to query the database and retrieve all records matching the search
>criteria over a period of time. For example, I need to obtain all records
>written within the past 24 hours matching my criteria. My initial thought
>was
>to use today - interval. However, I will need to be more specific concerning
>the time. I have a need to obtain records entered between 23:59:30 today and
>23:59:30 yesterday. Additionally, I may need records between varying
>timeframes, such as between yesterday at 4:00:00 PM and 16 hours prior to
>that
>time. This job will frequently run from cron as follows:
>
>--------------------
>#!/bin/ksh
>RUNDATE=`date +%D 23:59:30`
>INTERV=1
>
>SELECT assorted_fields
>FROM some_table
>WHERE some_condition
>AND datetime_field >= DATETIME("${RUNDATE}") - INTERVAL ($INTERV) DAY TO>SECOND
>AND datetime_field < DATETIME("${RUNDATE}");
>---------------------
>
>datetime_field is specified as year to second.
>
>RUNDATE and INTERV will be passed as arguments to the script and are shown
>here hardcoded for clarity and debug purposes.
>
>An additional thought was to use the BETWEEN function.
>
>datetime_field BETWEEN ("${RUNDATE1}") and ("${RUNDATE2}")
>
>Here the problem is obtaining the prior date and time field in UNIX given
>the fields variability.
>
>I am truely lost and would appreciate any help you can provide.
>
>Thanks in advance,
>
>Jeff McClure
What is the performance when you do a "select ... where datetime_field >=
"xxxxxxxx" and datetime_field < "xxxxxxxxx"? Also, is RUNDATE and INTERV a
field in the table being queried?
If the performance is better, then you could be getting much better performance
by comparing against a datetime variable in which you have already calculated
the two datetime fields.
7.11 is quite old at this point. If I can remember correctly, there was a
problem at one time in which we were re-evaluating the datetime field in a
query like this. It might be worth it to consider moving to a more recent
version.
Madison Pruet