Received: from dcave.digsys.bg (root@dcave.digsys.bg [192.92.129.5]) by hub.org (8.10.1/8.10.1) with ESMTP id e745sAG92843 for ; Fri, 4 Aug 2000 01:54:10 -0400 (EDT) Received: from dcave.digsys.bg (daniel@localhost.digsys.bg [127.0.0.1]) by dcave.digsys.bg (8.9.3/8.9.3) with ESMTP id IAA27141; Fri, 4 Aug 2000 08:53:50 +0300 (EEST) Message-Id: <200008040553.IAA27141@dcave.digsys.bg> X-Mailer: exmh version 2.1.0 09/18/1999 To: John McKown cc: pgsql-sql@postgresql.org Subject: Re: Extracting data by months In-reply-to: Your message of "Thu, 03 Aug 2000 14:23:34 CDT." Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Fri, 04 Aug 2000 08:53:50 +0300 From: Daniel Kalchev X-Archive-Number: 200008/34 Actually, PostgreSQL has specific function for this purpose. The query would be: SELECT * FROM table WHERE date_part('month', date_field) = '8'; (example to select month 8) If you use date_trunc, the selection will be by month/year - that is, date_trunc('month', date_field) will always result in the 1st monthday of the month. The year will be preserved. Daniel >>>John McKown said: > This might seem rather silly, but could you simply do something like: > > select * from database > where date_field >= '01/01/2000'::date > and date_field < '02/01/2000'::date; > > Of course, if date_field could contain many different years, then this > would not get you the result you wanted. > > John > > On Thu, 3 Aug 2000, Antti Linno wrote: > > > Lo. > > > > I'm in dire need of knowledge, how to extract data by month. Monthday > > and year arent' important, those I can't give from perl script, but what > > I do give to postgres are the numbers of the months. Date field is in > > timestamp. I thought about date_trunc, but I can't think of, how to get > > data with it. In script will be 12 queries, for each month, to get the > > news from that month. > > Btw, is there somewhere a manual about date_trunc, the docs that come with > > RH6.1 distribution are somewhat short and lacking explanation of > > date_trunc. > > Antti > > > > >