pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL Date Challenge
9+ messages / 6 participants
[nested] [flat]

* SQL Date Challenge
@ 2001-06-04 17:31 Josh Berkus <josh@agliodbs.com>
  2001-06-04 18:03 ` Re: SQL Date Challenge Manuel Sugawara <masm@fciencias.unam.mx>
  2001-06-04 18:44 ` Re: SQL Date Challenge george young <gry@ll.mit.edu>
  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 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   ` Re: SQL Date Challenge Larry Rosenman <ler@lerctr.org>
  2001-06-04 20:26   ` Re: SQL Date Challenge 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 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 18:03 ` Re: SQL Date Challenge Manuel Sugawara <masm@fciencias.unam.mx>
@ 2001-06-04 18:56   ` Larry Rosenman <ler@lerctr.org>
  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 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 18:03 ` Re: SQL Date Challenge Manuel Sugawara <masm@fciencias.unam.mx>
@ 2001-06-04 20:26   ` Josh Berkus <josh@agliodbs.com>
  2001-06-04 20:56     ` Re: SQL Date Challenge Alex Pilosov <alex@pilosoft.com>
  2001-06-04 21:30     ` Re: SQL Date Challenge Peter Eisentraut <peter_e@gmx.net>
  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 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 18:03 ` Re: SQL Date Challenge Manuel Sugawara <masm@fciencias.unam.mx>
  2001-06-04 20:26   ` Re: SQL Date Challenge Josh Berkus <josh@agliodbs.com>
@ 2001-06-04 20:56     ` Alex Pilosov <alex@pilosoft.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 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 18:03 ` Re: SQL Date Challenge Manuel Sugawara <masm@fciencias.unam.mx>
  2001-06-04 20:26   ` Re: SQL Date Challenge Josh Berkus <josh@agliodbs.com>
@ 2001-06-04 21:30     ` Peter Eisentraut <peter_e@gmx.net>
  2001-06-04 21:32       ` Re: SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-05 03:19       ` Re: SQL Date Challenge 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 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 18:03 ` Re: SQL Date Challenge Manuel Sugawara <masm@fciencias.unam.mx>
  2001-06-04 20:26   ` Re: SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 21:30     ` Re: SQL Date Challenge Peter Eisentraut <peter_e@gmx.net>
@ 2001-06-04 21:32       ` Josh Berkus <josh@agliodbs.com>
  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-04 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 18:03 ` Re: SQL Date Challenge Manuel Sugawara <masm@fciencias.unam.mx>
  2001-06-04 20:26   ` Re: SQL Date Challenge Josh Berkus <josh@agliodbs.com>
  2001-06-04 21:30     ` Re: SQL Date Challenge Peter Eisentraut <peter_e@gmx.net>
@ 2001-06-05 03:19       ` Josh Berkus <josh@agliodbs.com>
  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

* Re: SQL Date Challenge
  2001-06-04 17:31 SQL Date Challenge Josh Berkus <josh@agliodbs.com>
@ 2001-06-04 18:44 ` george young <gry@ll.mit.edu>
  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


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