Обсуждение: SQL Date Challenge
Folks,
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
Thanks for any suggestions!
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes: > > SELECT ALL Wednesdays BETWEEN 5/1/01 AND 6/1/01; > somthing like: select date from xx where to_char(date,'fmdy') = 'wed'; hth, Manuel.
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]
SELECT whatever from xx where extract(dow from date) = 3; LER >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 6/4/01, 1:03:11 PM, Manuel Sugawara <masm@fciencias.unam.mx> wrote regarding Re: [SQL] SQL Date Challenge: > "Josh Berkus" <josh@agliodbs.com> writes: > > > > SELECT ALL Wednesdays BETWEEN 5/1/01 AND 6/1/01; > > > somthing like: > select date from xx where to_char(date,'fmdy') = 'wed'; > hth, > Manuel. > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Folks, Thanks for your suggestions. Apparently I wasn't clear enough about what I'm trying to do: > > > > SELECT ALL Wednesdays BETWEEN 5/1/01 AND 6/1/01; > > > > somthing like: > > select date from xx where to_char(date,'fmdy') = 'wed'; This doesn't solve my problem, as the suggestion above presupposes that I have a temp table of all possible dates in the range, or misunderstands that I am trying to find all Wednesdays in column x. I am trying to list all Wednesdays that are *not* in column x. This requires me to build a list of all possible Wednesdays (within a date range), preferably *without* first having a table of all dates in existance! Some sort of manipulation of the date processor should be possible, shouldn't it? -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Mon, 4 Jun 2001, Josh Berkus wrote: > > This doesn't solve my problem, as the suggestion above presupposes that > I have a temp table of all possible dates in the range, or Yes, you must have such a table, or you have to iterate over existing table. > misunderstands that I am trying to find all Wednesdays in column x. > > I am trying to list all Wednesdays that are *not* in column x. > > This requires me to build a list of all possible Wednesdays (within a > date range), preferably *without* first having a table of all dates in > existance! Some sort of manipulation of the date processor should be > possible, shouldn't it? No. Same reason why you cannot write a select returning all values from 1 to 1000. You must have table to iterate over. When stored procedures are able to return sets (they do, but halfway: C ones can, but no other language supports that), you may have it a bit easier...
Josh Berkus writes: > I am trying to list all Wednesdays that are *not* in column x. > > This requires me to build a list of all possible Wednesdays (within a > date range), preferably *without* first having a table of all dates in > existance! Some sort of manipulation of the date processor should be > possible, shouldn't it? You can't easily build data out of nothing in a declarative way in SQL. Basically, if you want a list of data you either need to put them in a table (which you don't want) or list them in the command itself (which you can't). This isn't made easier by the fact that functions currently can't return sets without extreme wizardry. Your problem is probably solved best if some of the computation is shifted to the client side. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter, Alex, > You can't easily build data out of nothing in a declarative way in > SQL. > Basically, if you want a list of data you either need to put them in > a > table (which you don't want) or list them in the command itself > (which you > can't). This isn't made easier by the fact that functions currently > can't > return sets without extreme wizardry. Thanks for the feedback ... it's good to know at least when something is impossible. Looks like I'll have to build a nightly table of all Wednesdays in the last 90 days using PL/pgSQL. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Since so many people responded to my initial question, I thought I'd
post my procedural solution using PL/pgSQL (permission granted to
Roberto to acquire it).
I'm not gonna even try to explain the various references to my database
structure; there are too many. This is all from StaffOS, which may soon
be an Open-Source project near you:
CREATE FUNCTION if_create_timecards_due ()
RETURNS BOOLEAN AS '
DECLAREtc_period VARCHAR;tc_length INTERVAL;check_date DATE;first_date DATE;tc_window INTERVAL;first_week
DATE;week_endsINT4;wday_diff INT4;
BEGINtc_window := fn_get_admin_value(''timecard window'');tc_period := fn_get_admin_value(''timecard
period'');week_ends:= to_number(fn_get_admin_value(''week ends''),''9'')::INT4;IF tc_period ~* ''^weekly'' THEN
tc_length:= interval(''7 days''); first_date := current_date - tc_window;ELSE tc_length := interval(''14 days'');
first_week := to_date(fn_get_admin_value(''first week
ends''),''YYYY-MM-DD''); first_date := current_date - tc_window; IF (first_date - first_week)%14 < 7 then
first_date:= first_date + INTERVAL(''1 week''); END IF;END IF;wday_diff := extract(dow FROM first_date);IF wday_diff
<=week_ends THEN wday_diff := week_ends - wday_diff;ELSE wday_diff = 7 - wday_diff + week_ends;END IF;first_date
:=first_date + interval(to_char(wday_diff, ''9'') || ''
days'');check_date := first_date;
DELETE FROM timecard_due_dates;
WHILE check_date <= current_date LOOP INSERT INTO timecard_due_dates ( assignment_usq, week_ending ) SELECT
assignments.usq,check_date FROM assignments WHERE (status > 2 OR (status < 0 AND status > -81)) AND
start_date<= check_date AND end_date > (check_date - tc_length); check_date = check_date + interval(''7
days'');ENDLOOP;
RETURN TRUE;
END;'
LANGUAGE 'plpgsql';
ENjoy!
Josh
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco