Best way to do date comparisons

[b.sims]

I'm trying to write something which uses the Webinator query log table. In this, each entry is given its id as the hex value in seconds.

What I would like to be able to do is to run a query SELECT * where ID = a date to give the number of queries for a particular day.

I understand how to convert the ids to date format but not how to do in the other direction. What would be the best way to do this?

Thanks,
Ben

[John]

You can do direct date compares, e.g.

select * from querylog where id >= 'begin of yesterday' and id <= 'end of yesterday';
select * from querylog where id >= 'begin of 2002-01-31' and id <= 'end of 2002-01-31';

[b.sims]

Thanks, that works well, except:

It seems that 0 cannot be returned? For example, if I have an sql query which does not return any rows then do
<count $id>
then no new value of $ret is set. In this case, I would like $ret set to 0 so that I can display 0 queries on this date.

Ben

[mark]

Don't count answers from <sql> $loop already has the count.

[b.sims]

Great! One more on this topic: is there anyway to do a SELECT DISTINCT? I'd like to divide the number of records returned by the number of different dates.

[John]

After the results come back? There are probably several ways to do it, here's one:

<SQL "select * from querylog ...">
<strfmt %at "%Y-%m-%d" $id>
<xtree insert $ret>
<other processing of row>
</SQL>
<$nrecs=$loop>
<xtree ROW dump>
$ret.count records on $ret
</xtree>
<$ndays=$loop>
<$avgperday=($nrecs/$ndays)>