Re: Dates with unknown month and/or day

From: Nick Nick <nick7535(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Dates with unknown month and/or day
Date: 2007-11-06 07:55:47
Message-ID: BAY131-W25FA32DAE3CC7631F3EE1C4890@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


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.

_________________________________________________________________
Celeb spotting – Play CelebMashup and win cool prizes
https://www.celebmashup.com

Browse pgsql-novice by date

  From Date Subject
Next Message G. J. Walsh 2007-11-07 22:05:44 client authentication - password encryption
Previous Message Sean Davis 2007-11-05 17:41:31 Re: Dates with unknown month and/or day