Near as I can tell, there's no 'standard' way to store dates. SQLite's date functions can deal with dates as floating-point julian numbers, yyyy-mm-dd hh:mm:ss strings (with or without the time portion), or Unix time integers. As arguments to SQLite's date/time functions, Unix times usually have to be accompanied by a second argument, 'unixepoch'. In either format, SQLite's date/time functions internally convert yyyy-mm-dd... and Unix times into julian dates before evaluating.
So, without knowing anything about your specific requirements, the most experienced guys here usually recommend storing dates as julian numbers. It's clearly the most efficient in terms of storage, and effificncy. The downside, of course, is julian and Unix numbers are not human-readable as dates. But, if you need human-readable, yyyy-mm-dd hh:mm:ss, with or without the time portion works just as well if you're not tight on storage, and are willing to accept the negligible overhead of the internal conversions when you need to call a date function. Plus, yyyy-mm-dd... sorts, and behaves in boolean comparisons appropriately. -Clark ----- Original Message ---- From: sqlfan <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Saturday, April 5, 2008 3:44:37 PM Subject: Re: [sqlite] What is the standard way to store dates and do operations with dates please? is this just your "hack" or the standard way to do this? I don't need it to be floating point, since I'm not interested in "when" during the day. and, to be clear, "julian" is the calendar we all use, right? it's completely 1:1 with the ansi format 2008-04-05 that I mentioned, right? Thank you. Dennis Cote-2 wrote: > > sqlfan wrote: >> I'm very new to sqlite but I notice there is no way to mark a column as >> containing dates... What is the standard way to do operations with dates, >> please, and to store dates? Should I try the format 20080405 and do my >> own >> calculations using my language's standard library? (I'm using Python) or >> is >> there a better way to store dates? Thank you for all your help. I'm >> very >> new to all this. >> > See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions for info > on date and time functions. > > I would suggest storing dates as floating point julian day numbers. > > HTH > Dennis Cote > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/What-is-the-standard-way-to-store-dates-and-do-operations-with-dates-please--tp16514369p16518987.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
