SQL: filtering by date

naporeon

This is cross-posted from H/A, since on further consideration, I think this might have been a better place for it (mods, feel free to lock the H/A one, if you like).

Yesterday afternoon, I was given an assignment at work: write a PHP page that will generate a table of specific data, limited to items whose "milestone_target_start_date" (format: '2008-09-19 09:39:43') fall within a span of three calendar months, starting the month before today (whenever today is), and extending through this month and the next month after.

So today, the table would display all results whose "milestone target dates" fall in August, September, or October of 2008. Two weeks from now, it would display all results whose milestone target dates fall in September, October, or November of 2008. Basically I want to display a range of (current month - 1) to (current month + 1).

My problem is the fact that I know virtually nothing beyond the most basic SQL, and would have trouble enough extracting today's date, let alone generating a range that includes it. All of my in-office resources are out of the office today, so I've been limited to trial-and-error on this (using whatever I can find on http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html). Also, I'm concerned that once I DO get this, it will get borked when the span includes months in both December, 2008 and January, 2009.

Suggestions?


What I've got so far:

SELECT
  pdb.vw_protocol_milestones.protocol_name,
  pdb.vw_protocol_milestones.protocol_milestone_name,
  pdb.vw_protocol_milestones.milestone_target_start_date,
  pdb.vw_protocol_milestones.milestone_start_date,
  pdb.tbl_network.network_name,
  pdb.tbl_protocol_stage.stage_name
FROM
  pdb.vw_protocol_milestones
INNER JOIN pdb.tbl_protocol_stage ON (pdb.vw_protocol_milestones.protocol_stage = pdb.tbl_protocol_stage.protocol_stage_id)
INNER JOIN pdb.tbl_network ON (pdb.vw_protocol_milestones.network_id = pdb.tbl_network.network_id)
ORDER BY pdb.vw_protocol_milestones.milestone_target_start_date DESC

LittleBoots

Suggestion: There is a programming thread to post stuff like this in. Very helpful people in that thread.

http://forums.penny-arcade.com/showthread.php?t=40425 :P

naporeon

Thanks a bunch, LittleBoots.