On Thu, Oct 29, 2009 at 03:21:13PM -0500, Nicolas Williams scratched on the wall: > On Wed, Oct 28, 2009 at 07:11:29PM +0000, O'Neill, Owen wrote:
> > I must agree with other posters that the lack of an exposed timestamp > > type does feel like something of a gap. > > Given the rather large number of reasonable ways to represent > timestamps, I have to disagree. I'd rather have the freedom to use > whichever representation is best for my needs. I have to agree that more rigid database enforcement is not the solution. The question is a lot more complex than "we need a datetime datatype!". It isn't that simple. Most RDBMS products define a half-dozen or more native date/time datatypes. You usually have a date (day in time), a time (without a date) a datetime (point in time), a date-duration (interval of days), and a time-duration. Sometimes you also have stuff like a yearless date (e.g. a day of the year). Then there are the other odd ones, like epoch values. Then you have all those datatypes over again with or without additional timezone information. And then you have all kinds of messed up rules about how, if you have a datetime and want to add six hours, you need to add a time-duration, not a "time", and the result is another datetime. Or add a date and time to get a datetime. And you need a completely different system if you need to support an older calendar. If you think three-value-logic is confusing, just wait! And these rules (and how strict they are) is important... after all, the desire for stronger typing is what started all this. > What's needed is a _cheap_ function that can be used in a CHECK > constraint on timestamp columns. And, perhaps, a common convention for > naming types that correspond to specific timestamp representations. I think this is the right path. Rather than worrying about doubling the number of native datatypes in SQLite, it would be nice to see a clean, powerful, and well documented library of SQL functions that did all these conversions, verifications, and calculations. The existing functions do fairly well. They may be a bit expensive, but they tend to get the job done-- if you know how to use them correctly. This is where a number of people stand up and say the documentation is poor, which may be partly correct, but I think the real issue here is that dates, times, and timezones are hard. They sound very simple, and we all use them every day, so it feels like it just shouldn't be hard or complex. But it is. And making more and more datatypes won't fix that. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
