Re: [firebird-support] group by date

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On Fri, 12 Dec 2014 15:15:40 +0100, "'checkmail' check_m...@satron.de
[firebird-support]" <firebird-support@yahoogroups.com> wrote:
> Hello @ll,
> 
> I would like to group my records by day or month. The date is saved as
> timestamp. If I do the following, 
> 
> SELECT EXTRACT(day FROM m.messzeit), COUNT(*) as CountMessages
> FROM te_messzeiten m
> GROUP BY EXTRACT(day FROM m.messzeit)
> 
> I get all days (1 to 31), but I need group by 1.1.2014, 2.1.2014.. -
daily
> bzw. 1 2014, 2 2014 - monthly
> 
> How can I realize this?

group by day:

GROUP BY CAST(m.messzeit AS DATE)

or less efficient:

GROUP BY EXTRACT(YEAR from m.messzeit), (EXTRACT(month FROM m.messzeit),
EXTRACT(day from m.messzeit)

or if you want to group by month in year:

GROUP BY EXTRACT(YEAR from m.messzeit), (EXTRACT(month FROM m.messzeit)

Mark