Received: from noe.lyon.cemagref.fr (noe.lyon.cemagref.fr [195.221.113.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e5SBaV153284 for ; Wed, 28 Jun 2000 07:36:35 -0400 (EDT) Received: from lyon.cemagref.fr (IDENT:perreal@linux3.lyon.cemagref.fr [195.221.113.13]) by noe.lyon.cemagref.fr (8.9.3/jtpda-5.3.1) with ESMTP id NAA00674 ; Wed, 28 Jun 2000 13:38:36 +0200 (MET DST) Message-ID: <3959E2E7.D19FB802@lyon.cemagref.fr> Date: Wed, 28 Jun 2000 13:35:03 +0200 From: Guillaume =?iso-8859-1?Q?Perr=E9al?= Organization: Cemagref X-Mailer: Mozilla 4.72 [en] (X11; I; Linux 2.2.12-32 i686) X-Accept-Language: fr-FR, fr, en MIME-Version: 1.0 To: Antti Linno CC: pgsql-sql@postgresql.org Subject: Re: extract last months data References: Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200006/256 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