pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Extracting data by months
6+ messages / 5 participants
[nested] [flat]

* Extracting data by months
@ 2000-08-03 13:49 Antti Linno <alligator@all.ee>
  2000-08-03 19:23 ` Re: Extracting data by months John McKown <jmckown@prodigy.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Antti Linno @ 2000-08-03 13:49 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Extracting data by months
  2000-08-03 13:49 Extracting data by months Antti Linno <alligator@all.ee>
@ 2000-08-03 19:23 ` John McKown <jmckown@prodigy.net>
  2000-08-04 05:53   ` Re: Extracting data by months Daniel Kalchev <daniel@digsys.bg>
  2000-08-04 07:40   ` Re: Extracting data by months Sandis <sandis@mediaparks.lv>
  0 siblings, 2 replies; 6+ messages in thread

From: John McKown @ 2000-08-03 19:23 UTC (permalink / raw)
  To: pgsql-sql

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
> 
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Extracting data by months
  2000-08-03 13:49 Extracting data by months Antti Linno <alligator@all.ee>
  2000-08-03 19:23 ` Re: Extracting data by months John McKown <jmckown@prodigy.net>
@ 2000-08-04 05:53   ` Daniel Kalchev <daniel@digsys.bg>
  1 sibling, 0 replies; 6+ messages in thread

From: Daniel Kalchev @ 2000-08-04 05:53 UTC (permalink / raw)
  To: John McKown <jmckown@prodigy.net>; +Cc: pgsql-sql

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
 > > 
 > > 
 > 





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Extracting data by months
  2000-08-03 13:49 Extracting data by months Antti Linno <alligator@all.ee>
  2000-08-03 19:23 ` Re: Extracting data by months John McKown <jmckown@prodigy.net>
@ 2000-08-04 07:40   ` Sandis <sandis@mediaparks.lv>
  2000-08-04 13:24     ` Re: Extracting data by months Daniel Kalchev <daniel@digsys.bg>
  2000-08-04 14:46     ` Re: Extracting data by months Karel Zak <zakkr@zf.jcu.cz>
  1 sibling, 2 replies; 6+ messages in thread

From: Sandis @ 2000-08-04 07:40 UTC (permalink / raw)
  To: pgsql-sql

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
> >
> >
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Extracting data by months
  2000-08-03 13:49 Extracting data by months Antti Linno <alligator@all.ee>
  2000-08-03 19:23 ` Re: Extracting data by months John McKown <jmckown@prodigy.net>
  2000-08-04 07:40   ` Re: Extracting data by months Sandis <sandis@mediaparks.lv>
@ 2000-08-04 13:24     ` Daniel Kalchev <daniel@digsys.bg>
  1 sibling, 0 replies; 6+ messages in thread

From: Daniel Kalchev @ 2000-08-04 13:24 UTC (permalink / raw)
  To: Sandis <sandis@mediaparks.lv>; +Cc: pgsql-sql

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 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
 > > >
 > > >
 > >
 > 





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Extracting data by months
  2000-08-03 13:49 Extracting data by months Antti Linno <alligator@all.ee>
  2000-08-03 19:23 ` Re: Extracting data by months John McKown <jmckown@prodigy.net>
  2000-08-04 07:40   ` Re: Extracting data by months Sandis <sandis@mediaparks.lv>
@ 2000-08-04 14:46     ` Karel Zak <zakkr@zf.jcu.cz>
  1 sibling, 0 replies; 6+ messages in thread

From: Karel Zak @ 2000-08-04 14:46 UTC (permalink / raw)
  To: Sandis <sandis@mediaparks.lv>; +Cc: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2000-08-04 14:46 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-03 13:49 Extracting data by months Antti Linno <alligator@all.ee>
2000-08-03 19:23 ` John McKown <jmckown@prodigy.net>
2000-08-04 05:53   ` Daniel Kalchev <daniel@digsys.bg>
2000-08-04 07:40   ` Sandis <sandis@mediaparks.lv>
2000-08-04 13:24     ` Daniel Kalchev <daniel@digsys.bg>
2000-08-04 14:46     ` Karel Zak <zakkr@zf.jcu.cz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox