Re: extract last months data

Antti Linno wrote:

> Lo.
> Today I digged in manuals and other docs, but didn't find any hint, how
> to get data from table, where some cols are in date or datetime format,
> and the data was about last month, or about special month.
> Psql help was unhelpful too.
> Here's example:
> In table workers is data with different date. When I need to make summary,
> I just want to extract last months data, but I don't know whether the
> month ended with 29,30 or 31 (external program, that uses postgres),
> so I cant use
> select * from workers where date<31.1.2000 and date>1.1.2000
> I think I can get month and year number at least, but I can't use mday
> attribute.

Try:
SELECT * FROM workers WHERE DATE_TRUNC('month', date) = '2000-31-1'::DATE;

See the manual for exact syntax of DATE_TRUNC.

> All suggestions are welcome :P
>
> Stucked alligator :)

--
Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64