Re: Query vs. time
In <6ei6nk$qts$1@nnrp1.dejanews.com> jeff.w.mcclure@ameritech.com writes:
>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.
Not sure what your problem is here. Are you having trouble getting the
variables into the select statement ala;
echo "select * from table where field1 = '$VAR1' and field2 = '$VAR2'" |
dbaccess dbname -
or is the problem that you can't get the time stamp into the correct format
for the select?
--
Bryan Tonnet
batonnet@phase4.com.au