At 03:29 AM 20/10/2006, you wrote:

>This is probably an elementary question, but if I have a table of rows
>(millions) and they all have a TIMESTAMP column in them, and I want to get a
>total count of the number of rows that are in the table for the current
>month, are there any built in commands to do this in Firebird 1.5

First, let's assume that you want this count because you actually
want to do something with the number. If you're using the count as
an existence test, that's a flaw that you should abandon.

Otherwise....

There is no built-in function that does this directly but you can do
it with an expression like

where
extract (month from aTimestamp) = extract (month from CURRENT_TIMESTAMP)
and extract (year from aTimestamp) = extract (year from CURRENT_TIMESTAMP)

>, or do I
>have to write a stored procedure that will take into consideration the date
>ranges of each month of the year to determine if a row is a candidate for
>the current month?

Possibly that approach would be better-performing, since you could
loop through a subset acquired by an indexed search, e.g.

returns counter integer)
as
declare thisDate timestamp;
begin
...
counter = 0;
for select aTimestamp from aTable
where aTimestamp between (current_date - 32) and (current_date + 32)
into :thisDate do
begin
if (extract (month from thisDate) = extract (month from current_date)
and extract (year from thisDate = extract (year from
current_date)) then
counter = counter + 1;
end
end

As the saying goes, there is more than one way to kill a cat. If you
have to do this and/or other searches based on this criterion
frequently, or under performance-critical conditions, it could be
useful to write a before update or insert trigger that writes the
CCYY and MM values that you can index and query directly, e.g.

alter table aTable
add CCYY smallint.
add MM smallint;
commit;

create asc index asc_ccyy on aTable(CCYY,PrimaryKey);
create asc index asc_mm on aTable(MM,PrimaryKey);
/* and, if needed -- */
create desc index desc_ccyy on aTable(CCYY,PrimaryKey);
create desc index desc_mm on aTable(MM,PrimaryKey);
commit;

create trigger bui_aTable for aTable
active before insert or update
as
begin
if (new.aTimestamp is not null) then
begin
new.CCYY = extract (year from new.aTimestamp);
new.MM = extract (month from new.aTimestamp);
end
end

Then your search clause becomes

where CCYY = extract(year from current_date)
and MM = extract (month from current_date)

./hb