From mark.fenbers@noaa.gov Mon Oct  29 22:38:47 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mark.fenbers@noaa.gov>) id 1TSxyp-00085c-2L
	for pgsql-sql@postgresql.org; Mon, 29 Oct 2012 22:38:47 +0000
Received: from na3sys009aog103.obsmtp.com ([74.125.149.71])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mark.fenbers@noaa.gov>) id 1TSxyn-0007IK-Ic
	for pgsql-sql@postgresql.org; Mon, 29 Oct 2012 22:38:46 +0000
Received: from mail-qa0-f46.google.com ([209.85.216.46]) (using TLSv1) by
	na3sys009aob103.postini.com ([74.125.148.12]) with SMTP
	ID DSNKUI8Fc/XDqLofVyT91+lnZhd2K4kOkJLI@postini.com;
	Mon, 29 Oct 2012 15:38:45 PDT
Received: by mail-qa0-f46.google.com with SMTP id c26so1829515qad.19
	for <pgsql-sql@postgresql.org>; Mon, 29 Oct 2012 15:38:43 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:x-gm-message-state;
	bh=uX9lk97jxc3BFy1ZRimzlC+fR/IeLuNyiPS66wasvmo=;
	b=aQCdPSlbfCrk8mlQTY6hUisiSNvcihu0YzBSh7JFpCQIM2kxx9OoIiWk3fefVPJ/+z
	zsa+Wfd4iCNgL7XN3NulJaARDj5BNUmqvOZZD2ZOtSg8tdy8Jq/laPG+xorpsPrCcTQm
	vptPr9B85TBEBXUn7/778psGMI1SU9EehWfJhyB0RkoezY7+FXk/51eLIlmNgFD0lMDP
	NdKd66gxeMmw6nNfz1yg+UA/eNSpxsCGyeEkaJJT5RDThgrroc6Aq+cItMqH+7UuOutD
	oS/SM4aQtKbywlOxlZFc/h2l/3BQarejQ6dpghjO6/OhMqxNiLeljK73tjRnRyOap0Hy
	9JSQ==
Received: by 10.49.3.6 with SMTP id 6mr8307327qey.32.1351550323206;
	Mon, 29 Oct 2012 15:38:43 -0700 (PDT)
Received: from [198.206.42.50] (rrcs-24-172-201-241.central.biz.rr.com.
	[24.172.201.241])
	by mx.google.com with ESMTPS id cu14sm2033684qab.1.2012.10.29.15.38.42
	(version=TLSv1/SSLv3 cipher=OTHER);
	Mon, 29 Oct 2012 15:38:42 -0700 (PDT)
Message-ID: <508F0576.4020404@noaa.gov>
Date: Mon, 29 Oct 2012 18:38:46 -0400
From: Mark Fenbers <mark.fenbers@noaa.gov>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Fun with Dates
Content-Type: multipart/mixed; boundary="------------090405050005010407060402"
X-Gm-Message-State: 
 ALoCoQlj1CU29jaVCco0RWYWY9XmE46KSso7Ho999NbVW7gi5JRx6LFAgOKZb32sZrU3yoQZ+czJ
X-Pg-Spam-Score: -4.2 (----)
X-Archive-Number: 201210/57
X-Sequence-Number: 36928

This is a multi-part message in MIME format.
--------------090405050005010407060402
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#FFFFCC" text="#000000">
    Greetings,<br>
    <br>
    I want to be able to select all data going back to the beginning of
    the current month.&nbsp; The following portion of an SQL does NOT work,
    but more or less describes what I want...<br>
    <br>
    ... WHERE obstime &gt;= NOW() - INTERVAL (SELECT EXTRACT (DAY FROM
    NOW() ) ) + ' days'<br>
    <br>
    In other words, if today is the 29th of the month, I want to select
    data that is within 29 days old... WHERE obstime &gt;= NOW() -
    INTERVAL '29 days'<br>
    <br>
    How do I craft a query to do use a variable day of the month?<br>
    <br>
    Mark<br>
  </body>
</html>

--------------090405050005010407060402
Content-Type: text/x-vcard; charset=utf-8;
 name="mark_fenbers.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="mark_fenbers.vcf"

begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:Ohio River Forecast Center;Hydrometeorological Analysis & Support Unit
adr:1901 South OH-134;;National Weather Service;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Senior Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
url:weather.gov/ohrfc
version:2.1
end:vcard


--------------090405050005010407060402--




From spam_eater@gmx.net Mon Oct  29 22:43:48 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1TSy3f-0002n9-V4
	for pgsql-sql@postgresql.org; Mon, 29 Oct 2012 22:43:48 +0000
Received: from plane.gmane.org ([80.91.229.3])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1TSy3e-0007Mc-6S
	for pgsql-sql@postgresql.org; Mon, 29 Oct 2012 22:43:46 +0000
Received: from list by plane.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1TSy3g-00024F-K1
	for pgsql-sql@postgresql.org; Mon, 29 Oct 2012 23:43:48 +0100
Received: from ppp-188-174-101-16.dynamic.mnet-online.de ([188.174.101.16])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Mon, 29 Oct 2012 23:43:48 +0100
Received: from spam_eater by ppp-188-174-101-16.dynamic.mnet-online.de with
	local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Mon, 29 Oct 2012 23:43:48 +0100
X-Injected-Via-Gmane: http://gmane.org/
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
X-Complaints-To: usenet@ger.gmane.org
X-Gmane-NNTP-Posting-Host: ppp-188-174-101-16.dynamic.mnet-online.de
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>
X-Pg-Spam-Score: -1.1 (-)
X-Archive-Number: 201210/58
X-Sequence-Number: 36929

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






From mark.fenbers@noaa.gov Mon Oct  29 22:51:16 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mark.fenbers@noaa.gov>) id 1TSyAu-0006Os-Eu
	for pgsql-sql@postgresql.org; Mon, 29 Oct 2012 22:51:16 +0000
Received: from na3sys009aog120.obsmtp.com ([74.125.149.140])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mark.fenbers@noaa.gov>) id 1TSyAt-0007V4-AO
	for pgsql-sql@postgresql.org; Mon, 29 Oct 2012 22:51:15 +0000
Received: from mail-qa0-f46.google.com ([209.85.216.46]) (using TLSv1) by
	na3sys009aob120.postini.com ([74.125.148.12]) with SMTP
	ID DSNKUI8IYsARUPvrBUHws8IZtTK+srGDu2wi@postini.com;
	Mon, 29 Oct 2012 15:51:15 PDT
Received: by mail-qa0-f46.google.com with SMTP id c26so1835126qad.19
	for <pgsql-sql@postgresql.org>; Mon, 29 Oct 2012 15:51:13 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:x-gm-message-state;
	bh=vqas02AkVIkBa49bm2rfp7uvmwaSXGPC49i21uRV/7w=;
	b=a7KVeHgHnHDD7Epn8PT/B6Lue/o0aRpqRc2fOmowLcikvfkuswuhki6Xg462UkKPQq
	qEJreJWYaCXEnKsbeT2dMdC/HIOVdW6696QjnnQ/OirtKhB2jvGSbBiMm/AUqWQUS8AW
	2V3eRmIdsBlu0qJDVd47fgHStaN57vTMqFoRfiB+FRaJU6OoFYoS5kab51XSrkecMBym
	4c2chtSbiZ3VNcy8D3ffTIv0zbncPEWbpka8FBAqH4Z/uYUhGK3/K1ITp5xcEfMkdqQQ
	q9OK3UY+6bHXXoOVGP+wuDps8ZZz5WSjY3ehTdcshGn0NzuvYw+z1FhDsLvrH43/3v98
	u5tQ==
Received: by 10.49.29.103 with SMTP id j7mr23666770qeh.47.1351551073878;
	Mon, 29 Oct 2012 15:51:13 -0700 (PDT)
Received: from [198.206.42.50] (rrcs-24-172-201-241.central.biz.rr.com.
	[24.172.201.241])
	by mx.google.com with ESMTPS id p11sm6762566qaz.16.2012.10.29.15.51.13
	(version=TLSv1/SSLv3 cipher=OTHER);
	Mon, 29 Oct 2012 15:51:13 -0700 (PDT)
Message-ID: <508F0864.4040609@noaa.gov>
Date: Mon, 29 Oct 2012 18:51:16 -0400
From: Mark Fenbers <mark.fenbers@noaa.gov>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Fun with Dates
References: <508F0576.4020404@noaa.gov> <k6n0qf$c8f$1@ger.gmane.org>
In-Reply-To: <k6n0qf$c8f$1@ger.gmane.org>
Content-Type: multipart/mixed; boundary="------------090700040106050301090907"
X-Gm-Message-State: 
 ALoCoQkCKT7QJjKWB4l5PnPQMFLMAy1KyS+++Q6Cs5NiUwWqwT3TR+f5aqInQWha4y4Ap6B2jwKo
X-Pg-Spam-Score: -4.2 (----)
X-Archive-Number: 201210/59
X-Sequence-Number: 36930

This is a multi-part message in MIME format.
--------------090700040106050301090907
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFCC" text="#000000">
    <br>
    <blockquote cite="mid:k6n0qf$c8f$1@ger.gmane.org" type="cite">Or the
      other way round: anything that is equal or greater than the first
      <br>
      of the current month:
      <br>
      <br>
      select ...
      <br>
      from foobar
      <br>
      where obstime &gt;= date_trunc('month', current_date);
      <br>
    </blockquote>
    I knew it had to be something simple!   thanks!<br>
    Mark<br>
  </body>
</html>

--------------090700040106050301090907
Content-Type: text/x-vcard; charset=utf-8;
 name="mark_fenbers.vcf"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="mark_fenbers.vcf"

YmVnaW46dmNhcmQNCmZuOk1hcmsgRmVuYmVycw0KbjpGZW5iZXJzO01hcmsNCm9yZzpPaGlv
IFJpdmVyIEZvcmVjYXN0IENlbnRlcjtIeWRyb21ldGVvcm9sb2dpY2FsIEFuYWx5c2lzICYg
U3VwcG9ydCBVbml0DQphZHI6MTkwMSBTb3V0aCBPSC0xMzQ7O05hdGlvbmFsIFdlYXRoZXIg
U2VydmljZTtXaWxtaW5ndG9uO09IOzQ1MTc3LTk3MDg7VVNBDQplbWFpbDtpbnRlcm5ldDpN
YXJrLkZlbmJlcnNAbm9hYS5nb3YNCnRpdGxlOlNlbmlvciBNZXRlb3JvbG9naXN0DQp0ZWw7
d29yazo5MzctMzgzLTA0MzANCnRlbDtmYXg6OTM3LTM4My0wMDMzDQp1cmw6d2VhdGhlci5n
b3Yvb2hyZmMNCnZlcnNpb246Mi4xDQplbmQ6dmNhcmQNCg0K
--------------090700040106050301090907--