pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFun with Dates
3+ messages / 2 participants
[nested] [flat]
* Fun with Dates
@ 2012-10-29 22:38 Mark Fenbers <mark.fenbers@noaa.gov>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Fenbers @ 2012-10-29 22:38 UTC (permalink / raw)
To: pgsql-sql
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. The following portion of an SQL does NOT work,
but more or less describes what I want...<br>
<br>
... WHERE obstime >= 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 >= 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--
Attachments:
[text/x-vcard] mark_fenbers.vcf (345B, ../../508F0576.4020404@noaa.gov/2-mark_fenbers.vcf)
download | inline:
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Fun with Dates
@ 2012-10-29 22:44 Thomas Kellerer <spam_eater@gmx.net>
parent: Mark Fenbers <mark.fenbers@noaa.gov>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Kellerer @ 2012-10-29 22:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Fun with Dates
@ 2012-10-29 22:51 Mark Fenbers <mark.fenbers@noaa.gov>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Fenbers @ 2012-10-29 22:51 UTC (permalink / raw)
To: pgsql-sql
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 >= 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--
Attachments:
[text/x-vcard] mark_fenbers.vcf (360B, ../../508F0864.4040609@noaa.gov/2-mark_fenbers.vcf)
download | inline:
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-10-29 22:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-29 22:38 Fun with Dates Mark Fenbers <mark.fenbers@noaa.gov>
2012-10-29 22:44 ` Thomas Kellerer <spam_eater@gmx.net>
2012-10-29 22:51 ` Mark Fenbers <mark.fenbers@noaa.gov>
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