Thread: Extracting data by months
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
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 > >
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 couldcontain many different years, then this> would not get you the result you wanted.> > John> > On Thu, 3 Aug 2000, AnttiLinno 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. Datefield is in> > timestamp. I thought about date_trunc, but I can't think of, how to get> > data with it. In script willbe 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> > > > >
I do it like this: SELECT datums FROM jaunumi WHERE date_part('year',datetime(datums)) = '2000' AND date_part('month',datetime(datums)) = '08'; Where datums is a timestamp field. Regards, Sandis Jerics www.mediaparks.lv > 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 > > > > >
This is not exactly what the original question was about. You select the month is one specific year only. What you do it however easier achieved by: SELECT datums FROM jaunumi WHERE date_trunc('month', datums) = '2000-08-01'; datetime is an type full of tricks. :-) Daniel >>>"Sandis" said:> I do it like this:> > SELECT datums FROM jaunumi> WHERE date_part('year',datetime(datums)) = '2000' AND> date_part('month',datetime(datums)) = '08';> > Where datums is a timestamp field.> > Regards,> Sandis Jerics>www.mediaparks.lv> > > 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 databy month. Monthday> > > and year arent' important, those I can't give from perl script, but what> > > I do give to postgresare 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 andlacking explanation of> > > date_trunc.> > > Antti> > >> > >> >>
On Fri, 4 Aug 2000, Sandis wrote: > I do it like this: > > SELECT datums FROM jaunumi > WHERE date_part('year',datetime(datums)) = '2000' AND > date_part('month',datetime(datums)) = '08'; > > Where datums is a timestamp field. Or (in 7.0): SELECT datums FROM jaunum WHERE to_char('YYYY/MM', datums) = '2000/08'; ....becuase it more simple and it *will faster*. Karel