Thanks Martijn. I don't plan to use FB 1.5 in the recent future. FB
1.0 seems to have much more mature/tested codebase as far as I know
(please tell me if I'm wrong).

Two more questions:

1) Is CAST() the way to extract a timestamp's year/month/day/etc...?

2) Can I use computed columns to acomplish my goal? Example:

ALTER TABLE telefono
ADD ano
COMPUTED BY CAST(fecha AS YEAR); -- Or however you get the year

ALTER TABLE telefono
ADD mes
COMPUTED BY CAST(fecha AS MONTH); -- Or however you get the month

ALTER TABLE telefono
ADD dia
COMPUTED BY CAST(fecha AS DAY); -- Or however you get the day

ALTER TABLE telefono
ADD hora
COMPUTED BY CAST(fecha AS HOUR); -- Or however you get the hour

Then use a query like:

SELECT ano,
mes,
dia,
hora,
AVG(duracion)
FROM telefono
GROUP BY ano,
mes,
dia,
hora;

Regards,

Daniel Berstein.

--- In ib-support@y..., "Martijn Tonies" <m.tonies@u...> wrote:
> Hi,
>
> > I have a dialect 3 database (FB 1) with a table that has a
TIMESTAMP
> > column (fecha). I executed the following query:
> >
> > SELECT CAST(fecha AS DATE) AS fecha
> > FROM telefono
> > GROUP BY fecha;
> >
> > I would expect a result set of unique dates, but instead I
recieve
> > repeated rows. How come?
>
> You are grouping by the original column (fecha), not by the alias.
The
> GROUP BY of Firebird 1 can only group on existing columns.
>
> > I would like to build an histogram of the same table. The idea is
to
> > compute the number of rows in the table grouped by date and hour
of
> > the day. Is there any way I could do something like:
> > (I have no idea how to decompose a timestamp/date field into
year,
> > month, day, etc.)
> >
> > SELECT CAST(fecha AS YEAR) AS ano,
> > CAST(fecha AS MONTH) AS mes,
> > CAST(fecha AS DAY) AS dia,
> > CAST(fecha AS HOUR) AS hora,
> > AVG(duracion)
> > FROM telefono
> > GROUP BY CAST(fecha AS YEAR) AS ano,
> > CAST(fecha AS MONTH) AS mes,
> > CAST(fecha AS DAY) AS dia,
> > CAST(fecha AS HOUR) AS hora;
> >
>
> With Firebird 1.5 you will be able to use:
>
> GROUP BY 1, 2, 3, 4
>
>
>
>
> With regards,
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase
> http://www.upscene.com
>
> Firebird Workbench - the developer tool for Firebird
> http://www.upscene.com
>
> Upscene Productions
> http://www.upscene.com
>
> "This is an object-oriented system.
> If we change anything, the users object."