Daniel:

You're looking for EXTRACT. The syntax is EXTRACT( year/month/day from
date).

you can have fields defined like:

MyYear computed by (extract(year from YourDateField))

and use "group by" in there. I don't know about performance. I think that
it's better for you to use a field smallint (perhaps with an index in
year,month,day) and create some triggers to update this field.

Artur

----- Original Message -----
From: "danielberstein" <dbz@...>
To: <ib-support@yahoogroups.com>
Sent: Tuesday, October 15, 2002 10:35 PM
Subject: [ib-support] Re: CAST or GROUP BY error... or is this week becoming
too long for me :)


> 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
> >
> >
> >