To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Fun with Dates
Date: Mon, 29 Oct 2012 23:44:01 +0100
Lines: 24
Message-ID: <k6n0qf$c8f$1@ger.gmane.org>
References: <508F0576.4020404@noaa.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <508F0576.4020404@noaa.gov>

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