pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: george young <gry@ll.mit.edu>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Date Challenge
Date: Mon, 4 Jun 2001 14:44:18 -0400
Message-ID: <20010604144418.09f6e44a.gry@ll.mit.edu> (raw)
In-Reply-To: <web-66723@davinci.ethosmedia.com>
References: <web-66723@davinci.ethosmedia.com>

On Mon, 04 Jun 2001 10:31:52 -0700
"Josh Berkus" <josh@agliodbs.com> wrote:
> 	Can anyone come up with a purely declarative (i.e. SQL) way to SELECT
> all of the Wednesdays within a given time period?  Or is there, perhaps,
> some trick of the PGSQL date parser I could use?
> 
> 	I can think of a number of ways to do this procedurally, but that's
> very awkward for what I need to use the information (to select all
> wednesdays within the last two months for which each staff emember has
> not turned in a timecard).  I'm considering using a regularly updated
> reference table, but it seems like there *must* be a more elegant
> solution.
> 
> 	Basically, what I want is: 
> 
> SELECT ALL Wednesdays BETWEEN 5/1/01 AND 6/1/01;
> 
> 	Which results in:
> 
> Wednesdays
> -----------
> 5/2/01
> 5/9/01
> 5/16/01
> 5/23/01
> 5/30/01

Try (under postgres 7.1)
	select footable.somefield from footable where extract(dow from footable.my_date_field) = 3;

from the docs:

  EXTRACT (field FROM source)

  The extract function retrieves sub-fields from date/time values, such as year or hour. source is a value expression that
  evaluates to type timestamp or interval. (Expressions of type date or time will be cast to timestamp and can therefore be used
  as well.) field is an identifier (not a string!) that selects what field to extract from the source value. The extract function
  returns values of type double precision. The following are valid values: 
  ....
  The day of the week (0 - 6; Sunday is 0) (for timestamp values only) 

    SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
    Result: 5

-- 
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now?  [OK]



view thread (9+ messages)  latest in thread

Message-ID: <20010604144418.09f6e44a.gry@ll.mit.edu>
Permalink:  ../20010604144418.09f6e44a.gry@ll.mit.edu/
Also on:    postgresql.org/message-id/20010604144418.09f6e44a.gry@ll.mit.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gry@ll.mit.edu, josh@agliodbs.com
  Subject: Re: SQL Date Challenge
  In-Reply-To: <20010604144418.09f6e44a.gry@ll.mit.edu>
  References: <web-66723@davinci.ethosmedia.com> <20010604144418.09f6e44a.gry@ll.mit.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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