[SQL] Fun with Dates

2012-10-29 Thread Mark Fenbers 

  
  
Greetings,

I want to be able to select all data going back to the beginning of
the current month.  The following portion of an SQL does NOT work,
but more or less describes what I want...

... WHERE obstime >= NOW() - INTERVAL (SELECT EXTRACT (DAY FROM
NOW() ) ) + ' days'

In other words, if today is the 29th of the month, I want to select
data that is within 29 days old... WHERE obstime >= NOW() -
INTERVAL '29 days'

How do I craft a query to do use a variable day of the month?

Mark
  

<>
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Fun with Dates

2012-10-29 Thread Thomas Kellerer 

Mark Fenbers wrote on 29.10.2012 23:38:

Greetings,

I want to be able to select all data going back to the beginning of
the current month.  The following portion of an SQL does NOT work,
but more or less describes what I want...

... WHERE obstime >= NOW() - INTERVAL (SELECT EXTRACT (DAY FROM NOW()
) ) + ' days'

In other words, if today is the 29th of the month, I want to select
data that is within 29 days old... WHERE obstime >= NOW() - INTERVAL
'29 days'



Or the other way round: anything that is equal or greater than the first
of the current month:

select ...
from foobar
where obstime >= date_trunc('month', current_date);

Thomas




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Fun with Dates

2012-10-29 Thread Mark Fenbers 

  
  

Or the
  other way round: anything that is equal or greater than the first
  
  of the current month:
  
  
  select ...
  
  from foobar
  
  where obstime >= date_trunc('month', current_date);
  

I knew it had to be something simple!   thanks!
Mark
  

<>
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql