Обсуждение: Dates with unknown month and/or day

What is the best way to treat dates with unknown month and/or day?  I
have some historical data that has only year or year/month, while the
newest data has year/month/day.  If there is no other option than to set
unknowns to '1', that will work, but I wanted to make sure that there
was not another way (and I can't see how there could be given the way
that date functions work).

Thanks,
Sean

Christoph Della Valle wrote:
> Hi
>
> I think it is not a very good idea to fill unknown parts of a date with
> '1's, unless you introduce a new attribute "precision" or so, where you
> store which part (y for year only, m for year/month etc) of your date
> really can be taken seriously...
>
> Or you store year/month/day separately. When you want to use a date
> function, you concatenate all parts and cast it as a date. This is clean
> but more complicated. It depends, how often you need date-functions.
> Attention: concatenation with NULL results in NULL, so you need to use
> coalesce.

Thanks, Chris.  This is what I suspected.

Sean

Hi

I think it is not a very good idea to fill unknown parts of a date with '1's, unless you introduce a new attribute "precision" or so, where you store which part (y for year only, m for year/month etc) of your date really can be taken seriously...

Or you store year/month/day separately. When you want to use a date function, you concatenate all parts and cast it as a date. This is clean but more complicated. It depends, how often you need date-functions. Attention: concatenation with NULL results in NULL, so you need to use coalesce.

Yours,
Chris

-------- Original-Nachricht --------
Von: "Sean Davis" <sdavis2@mail.nih.gov>
An: "pg novice" <pgsql-novice@postgresql.org>
Betreff: [NOVICE] Dates with unknown month and/or day
Datum: 05/11/07 16:21

What is the best way to treat dates with unknown month and/or day? I
have some historical data that has only year or year/month, while the
newest data has year/month/day. If there is no other option than to set
unknowns to '1', that will work, but I wanted to make sure that there
was not another way (and I can't see how there could be given the way
that date functions work).

Thanks,
Sean

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Christoph Della Valle wrote:
> I think it is not a very good idea to fill unknown parts of a date with
> '1's, unless you introduce a new attribute "precision" or so, where you
> store which part (y for year only, m for year/month etc) of your date
> really can be taken seriously...
>
> Or you store year/month/day separately. When you want to use a date
> function, you concatenate all parts and cast it as a date. This is clean
> but more complicated. It depends, how often you need date-functions.
> Attention: concatenation with NULL results in NULL, so you need to use
> coalesce.

> -------- Original-Nachricht --------
> Von: "Sean Davis"
> What is the best way to treat dates with unknown month and/or day? I

I once did the database bit for an application where it was valid to
omit parts of dates either because an actual full date might not be
known and might not matter to the user.

I stored the dates as a number (yyyymmdd) putting 0's for omitted
parts. So March 20 2007 would be 20070320 and April 2007 would be
20070400. If all they had was a year then 2006 would simply be 20060000.

You couldn't omit something from the left, eg 20060010 (10th of unknown
month 2006) wasn't allowed.

There were pros and cons and maybe there would have been a better
approach. But it worked well enough for the purpose and everyone was
happy. The important thing was being able to order by date and pull out
everything for a particular year or month and that was easy as it was OK
to have unknown days appearing before the 1st of the month.

(One real-world thing it couldn't cope with, but they decided not to
bother about it, was where the uncertainty covered a range - March or
April 2006. One idea was, instead of a single date, to hold first and
last possible dates to cater for ranges. It was decided that was
overkill and that a comment field was good enough.)

Nick.

---

Get free emoticon packs and customisation from Windows Live. Pimp My Live!