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

* extract last months data
@ 2000-06-28 10:04 Antti Linno <alligator@all.ee>
  2000-06-28 11:35 ` Re: extract last months data Guillaume Perréal <perreal@lyon.cemagref.fr>
  2000-06-28 14:12 ` Re: extract last months data Thomas Lockhart <lockhart@alumni.caltech.edu>
  0 siblings, 2 replies; 3+ messages in thread

From: Antti Linno @ 2000-06-28 10:04 UTC (permalink / raw)
  To: pgsql-sql

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.
All suggestions are welcome :P

Stucked alligator :)





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

* Re: extract last months data
  2000-06-28 10:04 extract last months data Antti Linno <alligator@all.ee>
@ 2000-06-28 11:35 ` Guillaume Perréal <perreal@lyon.cemagref.fr>
  1 sibling, 0 replies; 3+ messages in thread

From: Guillaume Perréal @ 2000-06-28 11:35 UTC (permalink / raw)
  To: Antti Linno <alligator@all.ee>; +Cc: pgsql-sql

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





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

* Re: extract last months data
  2000-06-28 10:04 extract last months data Antti Linno <alligator@all.ee>
@ 2000-06-28 14:12 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 0 replies; 3+ messages in thread

From: Thomas Lockhart @ 2000-06-28 14:12 UTC (permalink / raw)
  To: Antti Linno <alligator@all.ee>; +Cc: pgsql-sql

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

  select * from t1
    where d >= (date_trunc('month', timestamp 'today')
        - interval '1 month')
      and d < date_trunc('month', timestamp 'today');

                    - Thomas



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


end of thread, other threads:[~2000-06-28 14:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-28 10:04 extract last months data Antti Linno <alligator@all.ee>
2000-06-28 11:35 ` Guillaume Perréal <perreal@lyon.cemagref.fr>
2000-06-28 14:12 ` Thomas Lockhart <lockhart@alumni.caltech.edu>

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