dates and databases
-
Hi there I am building some database layer code and trying to be as database independent as possible. One peeve is datetimes. Does it make sense to avoid the use of the datetime objects provided by the database provider altogether and use a double field? Then use persist the dates to the double field using the DateTime.ToOADate method? It seems to me then that I could easily do date comparisons such as "retrieve rows where date > " where the is converted to OA?
-
Hi there I am building some database layer code and trying to be as database independent as possible. One peeve is datetimes. Does it make sense to avoid the use of the datetime objects provided by the database provider altogether and use a double field? Then use persist the dates to the double field using the DateTime.ToOADate method? It seems to me then that I could easily do date comparisons such as "retrieve rows where date > " where the is converted to OA?
Why not just store the dates as ISO-8801 format?
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
Hi there I am building some database layer code and trying to be as database independent as possible. One peeve is datetimes. Does it make sense to avoid the use of the datetime objects provided by the database provider altogether and use a double field? Then use persist the dates to the double field using the DateTime.ToOADate method? It seems to me then that I could easily do date comparisons such as "retrieve rows where date > " where the is converted to OA?
Ryan Minor wrote:
Does it make sense
No, it doesn't. I use several different database systems and the only problem I've had with DateTimes is that they don't all agree on which range of dates to support. So unless you're storing historical (or far future) data, you probably won't have any trouble.
-
Why not just store the dates as ISO-8801 format?
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
peh. store dates as strings? what's wrong with the existing date and datetime types, they have sufficient support. IMO strings should be avoided for anything that isn't really a string, i.e. numbers, dates, ..., with a possible exception for small binary data encoded with say base64 (when BLOB isn't available or too expensive). and then of course one should use SQLparameters to provide data, not string literals, when querying with e.g. a WHERE clause. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
peh. store dates as strings? what's wrong with the existing date and datetime types, they have sufficient support. IMO strings should be avoided for anything that isn't really a string, i.e. numbers, dates, ..., with a possible exception for small binary data encoded with say base64 (when BLOB isn't available or too expensive). and then of course one should use SQLparameters to provide data, not string literals, when querying with e.g. a WHERE clause. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Luc Pattyn wrote:
what's wrong with the existing date and datetime types,
For one, MS SQL Server only supports a subset of
DateTimevalues, so your business layer needs to work around this before you store them in the db. Note, I'm not advocating store timestamps as strings. /raviMy new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
-
peh. store dates as strings? what's wrong with the existing date and datetime types, they have sufficient support. IMO strings should be avoided for anything that isn't really a string, i.e. numbers, dates, ..., with a possible exception for small binary data encoded with say base64 (when BLOB isn't available or too expensive). and then of course one should use SQLparameters to provide data, not string literals, when querying with e.g. a WHERE clause. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Except that bubba there wanted to make it db agnostic, and I've worked with a couple of databases in the past that have crap date and time support (hello Ingres, yes I'm talking about you, you malformed piece of relational dataloss crapturd).
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
Except that bubba there wanted to make it db agnostic, and I've worked with a couple of databases in the past that have crap date and time support (hello Ingres, yes I'm talking about you, you malformed piece of relational dataloss crapturd).
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
we could agree on Ingres not being a database then, hence no need to dumb down the whole world and turn agnostic into stupid. Next someone will find a "database" that can't handle real numbers; or strings of more than 6 characters; or Unicode... When he asks "Does it make sense to avoid the use of the datetime objects..." my answer is NO. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
we could agree on Ingres not being a database then, hence no need to dumb down the whole world and turn agnostic into stupid. Next someone will find a "database" that can't handle real numbers; or strings of more than 6 characters; or Unicode... When he asks "Does it make sense to avoid the use of the datetime objects..." my answer is NO. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Or one that doesn't have operator precedence?
-
Ryan Minor wrote:
Does it make sense
No, it doesn't. I use several different database systems and the only problem I've had with DateTimes is that they don't all agree on which range of dates to support. So unless you're storing historical (or far future) data, you probably won't have any trouble.
IMHO adding a non-exhaustive list of those DB systems would turn that into the perfect answer... :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
we could agree on Ingres not being a database then, hence no need to dumb down the whole world and turn agnostic into stupid. Next someone will find a "database" that can't handle real numbers; or strings of more than 6 characters; or Unicode... When he asks "Does it make sense to avoid the use of the datetime objects..." my answer is NO. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Luc Pattyn wrote:
When he asks "Does it make sense to avoid the use of the datetime objects..." my answer is NO.
I'd agree with that
Luc Pattyn wrote:
agree on Ingres not being a database
and that.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
Luc Pattyn wrote:
When he asks "Does it make sense to avoid the use of the datetime objects..." my answer is NO.
I'd agree with that
Luc Pattyn wrote:
agree on Ingres not being a database
and that.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
Pete O'Hanlon wrote:
I'd agree with that and that.
I'm glad to see you're back to normal; I suggest you keep ignoring the toaster. :laugh:
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
Pete O'Hanlon wrote:
I'd agree with that and that.
I'm glad to see you're back to normal; I suggest you keep ignoring the toaster. :laugh:
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Luc Pattyn wrote:
I suggest you keep ignoring the toaster.
I've kicked the hussy out. I only have eyes for the microwave now.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
Luc Pattyn wrote:
I suggest you keep ignoring the toaster.
I've kicked the hussy out. I only have eyes for the microwave now.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
Pete O'Hanlon wrote:
I only have eyes for the microwave now
I must warn you, microwaves can harm the eyes. And Ray-Ban won't offer adequate protection. :cool:
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
IMHO adding a non-exhaustive list of those DB systems would turn that into the perfect answer... :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Ooookaaay... I'll get right on that.
-
Hi there I am building some database layer code and trying to be as database independent as possible. One peeve is datetimes. Does it make sense to avoid the use of the datetime objects provided by the database provider altogether and use a double field? Then use persist the dates to the double field using the DateTime.ToOADate method? It seems to me then that I could easily do date comparisons such as "retrieve rows where date > " where the is converted to OA?
One thing to bear in mind is whether this database will be used for anything else, e.g. accessed directly from reporting tools rather than through your application code. In this case, holding the dates as a double may cause problems for the person writing the reports. Personally, I would consider making the data access layer pluggable, with a separate table gateway[^] for each RDBMS you are going to support. That way, if your database has good date type support you can use it, if not you can fall back on using doubles. Either way, the bulk of your code doesn't know and doesn't care. In fact, you might only need two gateways: one that supports date types and one that doesn't. Then you have a simple configuration switch to flick at deployment time to control how the application stores data. (Actually, that link above isn't the most helpful in the world. Buy the book, it's very good.)