Storing Dates

by Dave Cross

I seem to have spent a large part of the last year ranting about people who don't use database metadata properly. Usually I'm talking about things like primary keys, foreign keys and unique indexes, but recently I've come across a far more basic level of idiocy - using the wrong data types.

Currently part of my work involves taking data dumps from one database and loading it into another database (and then generating some reports from the data). Some of the fields involved contain dates. So being a sensible programmer I'm storing them in DATETIME columns in my database. It's simply a case of working out what format the other database uses as its default export format for datetime values and converting that to my database's default import format.

13 Comments

Bart van Kuik
2006-06-13 03:32:37
What's also fun is databases which have a separate date and time column. In and of itself fine if they're not related. However if they are, you have to combine them for selection and sorting each time. I've had the pleasure once and now make it a point to ALWAYS create a datetime column even if the time isn't required (it can be set to 00:00:00 if unknown).
Dan Zambonini
2006-06-13 06:11:28
Sometimes, though, customers do need to enter vague date data such as the examples you gave. If you're dealing with the heritage sector, for example, you may not know exactly when a certain object was created; only that it was 'circa 1916', or 'June 1964', maybe even '1200-1450'. On other occassions, you may know the exact date.


As the 'calendar' date select pop-up thing is so popular these days (on any flight/hotel site, etc), I think it would be interesting for someone to take a shot at standardising (or at least creating) a usable date-entry system whereby the user could choose a specific date (or date time), OR a more vague time/timespan (such as those I mention above). Ideally what we then need is for databases to have an ISO 8601 style date field (that can order/sort correctly, etc.), where we can store more complex date/time data (e.g. periods), rather than specific values.

Dave Cross
2006-06-13 06:20:12
Dan,


You're right of course. I've done a lot of genealogical research into my family so I know the importance of vague dates. I forgot that when writing this entry.


In most cases tho', I think that's not necessary. In the few cases when it is, perhaps it should be modelled with two fields - an exact date field and a "date description field" for vague dates. I like your idea for a new date field, but realistically that's not going to be an option for some considerable time.

Brian Jordan
2006-06-13 07:03:06
"There is no way of knowing what date is represented by "12-06-2006″."


I propose that there may be a way to guess. If you have a user associated to the row, it could be possible to examine the other rows entered by that user and then assume that the format is consistent with those dates when it is possible to determine the date. Hard to automate but not impossible.

Dave Cross
2006-06-13 07:25:56
I've got a better idea. Let's just cut the fingers off anyone who uses a middle-endian date.
John Carman
2006-06-13 16:42:26
The American form is logical when you understand that
whereas us Brits say '12th of June', most Americans
say 'June 12th'.
So we use '12-06', they use '06-12'.
Dave Cross
2006-06-14 00:53:58
John,


I understand completely why they do it. And I think it's perfectly acceptable to say either "12th January" or "January 12th". I think I use both of these about equally.


The problem is when you say "12/6" or "6/12". I know people in the US say that, but when you put it into an international computer system it just leads to confusion.


Either use the name of the month, or use the ISO date format of YYYY-MM-DD. Anything else is open to misinterpretation.

Lonnie Olson
2006-06-14 11:05:35
What about using Unix timestamps? They are universal, easily portable, and make date calculation/comparison really easy.
Adam Kennedy
2006-06-14 13:02:56
Unix (or any native style) timestamps have their own problems. For example, they are all in UTC, so not only do you lose any timezone information (eg, the timezone an event occured in) but you lose the ability to handle things like the floating timezone.


If you have to store a datetime in an untyped format (i.e. string) then you should always do it in ISO format. That's what it was invented for.

John Carman
2006-06-14 17:55:52
Dave,
I quite agree with the use of the ISO format.
It sorts correctly even if it is in a text column.
Unfortunately I always get to use databases that have
been designed by 'experts'.
Dave Cross
2006-06-15 05:18:54
The other problem with Unix timestamps is that if you want to use dates earlier than 1970 then you have to use negative numbers - and many systems don't handle that well.
Ed
2006-06-23 18:11:40
My favroite is year month day, all run together - 20060623 because it sorts nicely, even as text.
David Wurmfeld
2006-06-26 05:25:08
Just a thought, but why not the "star date"? That way we will be really ahead of the curve! Not to be toungue in cheek, but really folks, use DATETIME. Until warp drive arrives, it is the most direct date object.