pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Date Challenge
9+ messages / 6 participants
[nested] [flat]
* SQL Date Challenge
@ 2001-06-04 17:31 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 9+ messages in thread
From: Josh Berkus @ 2001-06-04 17:31 UTC (permalink / raw)
To: pgsql-sql
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 josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-04 18:03 Manuel Sugawara <masm@fciencias.unam.mx>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 9+ messages in thread
From: Manuel Sugawara @ 2001-06-04 18:03 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-04 18:44 george young <gry@ll.mit.edu>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 9+ messages in thread
From: george young @ 2001-06-04 18:44 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-04 18:56 Larry Rosenman <ler@lerctr.org>
parent: Manuel Sugawara <masm@fciencias.unam.mx>
1 sibling, 0 replies; 9+ messages in thread
From: Larry Rosenman @ 2001-06-04 18:56 UTC (permalink / raw)
To: Manuel Sugawara <masm@fciencias.unam.mx>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-04 20:26 Josh Berkus <josh@agliodbs.com>
parent: Manuel Sugawara <masm@fciencias.unam.mx>
1 sibling, 2 replies; 9+ messages in thread
From: Josh Berkus @ 2001-06-04 20:26 UTC (permalink / raw)
To: pgsql-sql
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 josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-04 20:56 Alex Pilosov <alex@pilosoft.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 9+ messages in thread
From: Alex Pilosov @ 2001-06-04 20:56 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-04 21:30 Peter Eisentraut <peter_e@gmx.net>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 9+ messages in thread
From: Peter Eisentraut @ 2001-06-04 21:30 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-04 21:32 Josh Berkus <josh@agliodbs.com>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2001-06-04 21:32 UTC (permalink / raw)
To: pgsql-sql
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 josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Date Challenge
@ 2001-06-05 03:19 Josh Berkus <josh@agliodbs.com>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2001-06-05 03:19 UTC (permalink / raw)
To: pgsql-sql
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''),''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'');
END LOOP;
RETURN TRUE;
END;'
LANGUAGE 'plpgsql';
ENjoy!
Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2001-06-05 03:19 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-04 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
2001-06-04 18:03 ` Manuel Sugawara <masm@fciencias.unam.mx>
2001-06-04 18:56 ` Larry Rosenman <ler@lerctr.org>
2001-06-04 20:26 ` Josh Berkus <josh@agliodbs.com>
2001-06-04 20:56 ` Alex Pilosov <alex@pilosoft.com>
2001-06-04 21:30 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-04 21:32 ` Josh Berkus <josh@agliodbs.com>
2001-06-05 03:19 ` Josh Berkus <josh@agliodbs.com>
2001-06-04 18:44 ` george young <gry@ll.mit.edu>
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