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
thatevaluates to type timestamp or interval. (Expressions of type date or time will be cast to timestamp and can
thereforebe 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
theweek (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]