Dates, Times, and Databases

Thread view:

Matt Hughes - 05 Nov 2004 17:17 GMT

I have recently been asked to add a datetime column to my database
schema and support searching on it through a GUI. My problem is that
sometimes people will enter just a date in the column, sometimes a
date with just the hour, and sometimes maybe they'll want to enter all
the datetime information down to the millisecond.

Of course, if someone enters in the search field just a date
'11/04/2004' they want all the rows in that table that match that date
regardless of time. However, if they get more specific, such as
'11/04/2004 2:00' they want all the rows that match that date and time
regardless of the trailing time units (seconds, milliseconds).

However, that is not how databases currently work with datetime in my
experience. If you have a datetime field and the entry is '11/04/2004
2:00 PM' and try to search where field='11/04/2004' it will not match
that entry. Has anyone dealt with this situation and created any sort
of solution. It seems that databases should be much smarter about
this.

Reply to this Message

Lothar Kimmeringer - 05 Nov 2004 17:20 GMT

> However, that is not how databases currently work with datetime in my
> experience. If you have a datetime field and the entry is '11/04/2004
> 2:00 PM' and try to search where field='11/04/2004' it will not match
> that entry. Has anyone dealt with this situation and created any sort
> of solution. It seems that databases should be much smarter about
> this.

You should work with PreparedStatements and use setTimestamp for
the specific column in the where-clause. Everything else should
work fine, because the JDBC-driver should care about the corrent
"formatting" of the timestamp.

Regards, Lothar

Signature

Lothar Kimmeringer E-Mail: spamfang@kimmeringer.de
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!

Reply to this Message

Daniel Dittmar - 05 Nov 2004 18:57 GMT

> I have recently been asked to add a datetime column to my database
> schema and support searching on it through a GUI. My problem is that
[quoted text clipped - 14 lines]
> of solution. It seems that databases should be much smarter about
> this.

Search for a range. Something like
WHERE col >= '11/04/2004' and col < ADDDAY ('11/04/2004', 1)

You still have to look at the date to know if you have to add a month, a
day or an hour. You could also do the date manipulation in Java, but I'm
not sure that datevar.setDate (datevar.getDate () + 1) does the right
thing with refards to month.

Another idea would be to use your own implementation of data columns as
strings (YYYYMMDDHHMMSSmmmmm), then you could use LIKE. But that's
probably more work than using ranges in your query.

> of solution. It seems that databases should be much smarter about
> this.

Different people have different ideas about 'the right thing to do'. The
current implementation is at least consistent with other data types.

Daniel

Reply to this Message

Dates, Times, and Databases

Free Magazines