Thread: HOW-TO do incomplete dates: year, optional month, optional day?
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/ !
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
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.
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/ !
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/ !