Horton

Thread

  1. HOW-TO do incomplete dates: year, optional month, optional day?

    Will Trillich <will@serensoft.com> — 2003-01-22T22:11:09Z

    is there a way, save lots of manual manipulation and
hand-waving, to implement PARTIAL DATES? (this may be exactly
the job for creating a new data type, but hopefully someone's
already invented this wheel...?)

	"doug has worked at pinnacle since 1991".

not 1-jan-1991, not 31-dec-1991, but just plain-ol' 1991.

	"beulah started here back in november of 1998."

not 1-november, not 30-november. just november, of 1999.

	"my first day at acme corp was the 17th of may, 2001."

here, in that same field, we need year, month AND day.

at the moment i'm considering views and rules (with plpgsql
functions to do the re-assembly). here's a seat-of-the-pants
recreation (no syntax checking) to show my gyrations:

CREATE TABLE partial_dates(
	-- yada yada
	partial_year  INTEGER, -- zero/null or 1492, 2001...
	partial_month SMALLINT,-- zero/null, or 1-12
	partial_day   SMALLINT -- zero/null, or 1-31
	-- yada yada
);

CREATE FUNCTION
	partial_date(integer,smallint,smallint) -- y,m,d
RETURNS TEXT as '
DECLARE
	yr ALIAS FOR $1;
	mo ALIAS FOR $2;
	dy ALIAS FOR $3;
	month TEXT;
BEGIN
	IF yr < 1900 THEN
		RETURN ''UNKNOWN'';
	END IF;
	IF mo < 1 THEN
		RETURN yr || text;
	END IF;
	SELECT INTO month abbr_en FROM month_table WHERE num = mo;
	IF dy < 1 THEN
		RETURN yr::text || ''-'' || month;
	END IF;
	RETURN yr::text || ''-'' || month || ''-'' || dy::text;
END;
' language 'plpgsql';

CREATE VIEW dates AS
SELECT
	-- yada yada
	partial_date(partial_year, partial_month, partial_day)
	-- yada yada
FROM
	partial_dates;

is there a better way?

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

  2. Re: HOW-TO do incomplete dates: year, optional month, optional day?

    Richard Huxton <dev@archonet.com> — 2003-01-23T11:34:27Z

    On Wednesday 22 Jan 2003 10:11 pm, will trillich wrote:
> is there a way, save lots of manual manipulation and
> hand-waving, to implement PARTIAL DATES? (this may be exactly
> the job for creating a new data type, but hopefully someone's
> already invented this wheel...?)
>
> 	"doug has worked at pinnacle since 1991".
>
> not 1-jan-1991, not 31-dec-1991, but just plain-ol' 1991.
>
> 	"beulah started here back in november of 1998."
>
> not 1-november, not 30-november. just november, of 1999.
>
> 	"my first day at acme corp was the 17th of may, 2001."
>
> here, in that same field, we need year, month AND day.
>
> at the moment i'm considering views and rules (with plpgsql
> functions to do the re-assembly). here's a seat-of-the-pants
> recreation (no syntax checking) to show my gyrations:
>
> CREATE TABLE partial_dates(
> 	-- yada yada
> 	partial_year  INTEGER, -- zero/null or 1492, 2001...
> 	partial_month SMALLINT,-- zero/null, or 1-12
> 	partial_day   SMALLINT -- zero/null, or 1-31
> 	-- yada yada
> );

The only other thing I can think of would be to store it all as an INT4, so 
for the examples above you'd store

19910000
19981100
20010517

So you're using the zeroes as n/a but still keeping the value as one column. 
Add a check function valid_partial_date(..) and a display fn 
show_partial_date(...)

The other alternative would be to store a text representation of the date, so 
you don't need to translate when viewing, but that would mean more parsing 
when checking new values.

PS - I'd use the new CREATE DOMAIN feature of 7.3 to name the type ready for 
checks on domains in a later release - when that's done it'll be much 
cleaner.

-- 
  Richard Huxton

  3. Re: HOW-TO do incomplete dates: year, optional month, optional day?

    Bruno Wolff III <bruno@wolff.to> — 2003-01-23T13:45:27Z

    On Wed, Jan 22, 2003 at 16:11:09 -0600,
  will trillich <will@serensoft.com> wrote:
> is there a way, save lots of manual manipulation and
> hand-waving, to implement PARTIAL DATES? (this may be exactly
> the job for creating a new data type, but hopefully someone's
> already invented this wheel...?)
> 
> 	"doug has worked at pinnacle since 1991".
> 
> not 1-jan-1991, not 31-dec-1991, but just plain-ol' 1991.

I think if you precisely define what just plain-ol' 1991 means, you
will be able to design a solution that will work.

A couple possible answers are:

Store a lower and upper bound for the dates.

Store a best guess for the date, but only output the most significant
part(s) in reports.

  4. Re: HOW-TO do incomplete dates: year, optional month, optional day?

    Will Trillich <will@serensoft.com> — 2003-01-23T17:48:27Z

    On Thu, Jan 23, 2003 at 11:34:27AM +0000, Richard Huxton wrote:
> On Wednesday 22 Jan 2003 10:11 pm, will trillich wrote:
> > is there a way, save lots of manual manipulation and
> > hand-waving, to implement PARTIAL DATES?
all of these three, below, would go into the same field in a
table (different records, of course :)

> > 	"doug has worked at pinnacle since 1991".
> > not 1-jan-1991, not 31-dec-1991, but just plain-ol' 1991.
> >
> > 	"beulah started here back in november of 1998."
> > not 1-november, not 30-november. just november, of 1999.
> >
> > 	"my first day at acme corp was the 17th of may, 2001."
> > here, in that same field, we need year, month AND day.

> The only other thing I can think of would be to store it all
> as an INT4, so for the examples above you'd store
> 
> 19910000
> 19981100
> 20010517
> 
> So you're using the zeroes as n/a but still keeping the value as one column. 
> Add a check function valid_partial_date(..) and a display fn 
> show_partial_date(...)

hey, that's a good idea! or maybe store the year in the first
two bytes, the month in byte three, and the day in byte four.
that'd be pretty simple... maybe i should define a new type?

	CREATE TYPE partial_date ...

if so, i'm a bit in the dark as to what kind of functions i need
to create -- the docs for "create type" mention needing
functions for input and output, and apparently they need to
return "opaque"? in which case, how do they return values?  and
what are the arguments passed? i need an example to go by...

anybody got some examples of "input/output" functions for
creating types?

> PS - I'd use the new CREATE DOMAIN feature of 7.3 to name the type ready for 
> checks on domains in a later release - when that's done it'll be much 
> cleaner.

hmm. i'm still on 7.2.1 as yet (debian "we-dont-upgrade-until-its-
calcified-thoroughly" stable). sounds quite intriguing...

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

  5. Re: HOW-TO do incomplete dates: year, optional month, optional day?

    Will Trillich <will@serensoft.com> — 2003-01-23T20:20:43Z

    On Thu, Jan 23, 2003 at 11:48:27AM -0600, will trillich wrote:
> On Thu, Jan 23, 2003 at 11:34:27AM +0000, Richard Huxton wrote:
> > On Wednesday 22 Jan 2003 10:11 pm, will trillich wrote:
> > > is there a way, save lots of manual manipulation and
> > > hand-waving, to implement PARTIAL DATES?
> 
> > The only other thing I can think of would be to store it all
> > as an INT4, so for the examples above you'd store
> > 
> > 19910000
> > 19981100
> > 20010517
> > 
> > So you're using the zeroes as n/a but still keeping the value as one column. 
> > Add a check function valid_partial_date(..) and a display fn 
> > show_partial_date(...)
> 
> hey, that's a good idea! or maybe store the year in the first
> two bytes, the month in byte three, and the day in byte four.
> that'd be pretty simple... maybe i should define a new type?
> 
> 	CREATE TYPE partial_date ...

i finally found the xtypes.html on postgresql.org/idocs and it
gives examples of C functions. is it a bad idea to use plpgsql
as the language for creating types?

if so, where do i find the #includes and !gcc command options to
pass so it won't gripe about not finding "main()"?

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !