Hi,

I need to store "dates" -- but, with the capability
of leaving portions as "unknown".

The easiest example I can think of to illustrate
this would be tracking birthdates -- often you will
know the month+day but not the *year*. Or, even
just a month and a year (based on knowing current
*age*).

Of course, I can just create three numeric fields
(month, day, year). But, what is the "best" way
to ensure the contents of that *set* of fields
makes sense? E.g., the month is constrained to
[1..12], day constrained to a value determined
by month (and year), etc. Should I just define
triggers for each that examine their counterparts
for validity checks, etc.? Or, define a new *type*
that implicitly does this sort of thing? (note the
consequences of each approach when it comes to
sorting, etc.)

Thanks,
--don

On 2011-02-18 06:17, D Yuniskis wrote:
[...]
Soem ideas, you can use check constraints, example:

CHECK ( MONTH BETWEEN 1 AND 12 )

If you let unknown parts be represented by null you can "build" a date
from the parts and check that it is after some historical date

CHECK ( cast(<constructed_date> as date) > '1900-01-01' )

If any of the parts is null, the constructed date will be null and the
check will evaluate to null, which is ok. If the constructed date is
invalid the cast will generate an exception, preventing invalid dates.

At the moment I do not have a psql installation to verify against, so I
wont try to construct psql code from the above.


/Lennart

[...]

D Yuniskis, 18.02.2011 06:17:

I wonder if you could use a combination of a date field and flags to indicate if part of the date is valid.

So you'd have the columns:
the_date date
month_valid boolean
year_valid boolean
day_valid boolean

So if you just know the year and month, you use 2011-02-01 and set day_valid = false
If you just know the day and month you use 1900-04-14 and set year_valid = false

Not sure how complicated this would be to use though, but at least you can the automatic date checking without any further problems.

A problem would be a February 29th (when only getting day and month)
You would need to make sure you create a "dummy year" that is a leap year.

Regards
Thomas

On Thu, 17 Feb 2011 22:17:49 -0700, D Yuniskis wrote:

You can use interval types. They're created for just that situation. The
description is here:

http://www.postgresql.org/docs/9.0/s...-datetime.html



--
http://mgogala.byethost5.com

On 2/18/2011 9:14 AM, Mladen Gogala wrote:
I don't think that will work. Some common, everyday scenarios:

"Schedule your next appointment 6 months from now" (i.e., "sometime
in August, 2011" -- month and year known but not day)

"She was born in January" (i.e., month known but day and year unknown)

"She was born in January and is presently 21 years old" (i.e., month
known, day unknown and year derived from "today - 21yrs")

"Their wedding anniversary is September 4th" (i.e., day and month known
but not year)

"They've been married for 60 years" (i.e., day and month unknown but
year approximated by "today - 60")

"They were married in 1951" (i.e., year known but month and day unknown)

With three "values", there are 8 (?) ambiguity possibilities...

On 2/18/2011 5:16 AM, Thomas Kellerer wrote:
I thought of doing this in a new data type -- "date with ambiguity" :>
So, things like 02/18/2011, 02/XX/2011, 02/18/XXXX, XX/XX/2011, etc
would be possible. (i.e., parse the 'X's to determine the states of
the three flags you describe above)

But, how do you define a sorting criteria for these values?
I.e., if you want to put them in absolute chronological order,
you would probably have to treat any unknown *days* as if they
were "01", months as "01", etc.

This simplification wouldn't work, though. E.g., if you wanted
to know what was on your "calendar" (see my reply to Mladen Gogala
for examples), 02/18/2011 and 02/18/XXXX should be treated the same
as "today" (it being 02/18/2011 as I write this). And, treating
02/XX/2011 as 02/01/2011 would make it a *past* event -- whereas
02/XX/2011 could actually equally represent 02/28/2011!

I think leaving the three data as separate fields lets the
application define a sorting criteria for the *set* of values
that is most appropriate for that application. Without
complicating it by forcing it to extract day/month/year from
a bogus "date" value. (?)

Or, "October 5" when the year is omitted.

> You would need to make sure you create a "dummy year" that is a leap year.

D Yuniskis wrote on 18.02.2011 22:12:
October 5 is not a problem with my solution as that is valid for any year, whereas February 29th is only valid every 4 years.

On 2/18/2011 2:57 PM, Hans Castorp wrote:
Try 1582. :>