Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
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.)


Celeb spotting – Play CelebMashup and win cool prizes

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group