[SQL] 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 information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PGAccess/pgplsql Blues
Roberto, > IIRC, pgaccess does quote-escaping for you, so if you try to write > "standard" PL/pgSQL (escaping single quotes), it'll barf this error. > > Just something to check. Thanks. This doesn't seem to be the case; it seems to be a translation problem: 1. Test fn_save_order: it's working. 2. Open fn_save_order in PGAccess. 3. Add '--test comment' on its own line. 4. Save fn_save_order. 5. test it: "Parse Error at or near "" " Unfortunately, I can't afford to pay Constatin for debugging, so that's where things stand ... -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Date Challenge
"Josh Berkus" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
Re: [SQL] SQL Date Challenge
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 information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL Date Challenge
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 information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Huh? Data typing bug?
Folks, I just subtracted two dates and got an INT4, rather than the INTERVAL I was expecting. What goes on here? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Huh? Data typing bug?
You got difference in seconds as the result? Show some examples. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Huh? Data typing bug?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I just subtracted two dates and got an INT4, rather than the INTERVAL I > was expecting. What goes on here? IIRC, number of days (as an int) is what that's supposed to produce. If that's not what you wanted, maybe you ought to cast the dates to timestamp or some such. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Huh? Data typing bug?
Tom, > IIRC, number of days (as an int) is what that's supposed to produce. > > If that's not what you wanted, maybe you ought to cast the dates to > timestamp or some such. I see. It was never made clear to me that here the DATE type differs from DATETIME and TIMESTAMP significantly. This makes some sort of sense, now. Correct me if I'm wrong: DATE + INT4 = DATE DATE - DATE = INT4 But: DATETIME + INTERVAL = DATETIME DATETIME - DATETIME = INTERVAL -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Date Challenge
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 '
DECLARE
tc_period VARCHAR;
tc_length INTERVAL;
check_date DATE;
first_date DATE;
tc_window INTERVAL;
first_week DATE;
week_ends INT4;
wday_diff INT4;
BEGIN
tc_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''),''-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'');
END LOOP;
RETURN TRUE;
END;'
LANGUAGE 'plpgsql';
ENjoy!
Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
