Re: [sqlite] What is the standard way to store dates and do operations with dates please?
* Dennis Cote: > From the wikipedia article you cited: > > Note: although many references say that the Julian in "Julian day" > refers to Scaliger's father, Julius Scaliger, in the introduction to > Book V of his Opus de Emendatione Temporum ("Work on the Emendation of > Time") he states, "Iulianum vocavimus: quia ad annum Iulianum dumtaxat > accomodata est", which translates more or less as "We have called it > Julian merely because it is accommodated to the Julian year." This > Julian refers to Julius Caesar, who introduced the Julian calendar in 46 BC. > > I can't vouch for the veracity of this note, but he he seems to know > what he is talking about and has given what is purported to be a > reference from the original author that backs his claim (as best I can > tell from the quoted Latin and its translation). As always you have to > take everything on wikipedia with a grain of salt, but this looks > authoritative. "Calendrical Calculations" by Reingold and Dershowitz has the following to say about the matter: | It is often claimed [...] that Scaliger named the [Julian] period [a | method of counting years] after his father, the Renaissance physician | Julius Cæsar Scaliger, but this claim is not borne out by examination | of Scaliger's great work, /De Emendatione Temporum/, from which the | section quote above [Iulianam vocavimus: quia ad annum Iulianum | dumtaxat accommodata est] is taken. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
On Apr 7, 2008, at 6:27 PM, Dennis Cote wrote: > D. Richard Hipp wrote: >> >> See http://en.wikipedia.org/wiki/Julian_day >> >> Note that "Julian" in Julian Day Number and Julian Calendar >> refer to two different people named Julius. The Julian Day Number >> Julian is Julius Scaliger, the father of the guy who invented >> the julian day number in 1583. Julian in Julian Calendar refers to >> Julius Caesar, the Roman emperor. >> > > Richard, > > From the wikipedia article you cited: > > Note: although many references say that the Julian in "Julian day" > refers to Scaliger's father, Julius Scaliger, in the introduction to > Book V of his Opus de Emendatione Temporum ("Work on the Emendation of > Time") he states, "Iulianum vocavimus: quia ad annum Iulianum dumtaxat > accomodata est", which translates more or less as "We have called it > Julian merely because it is accommodated to the Julian year." This > Julian refers to Julius Caesar, who introduced the Julian calendar > in 46 BC. > > I can't vouch for the veracity of this note, but he he seems to know > what he is talking about and has given what is purported to be a > reference from the original author that backs his claim (as best I can > tell from the quoted Latin and its translation). As always you have to > take everything on wikipedia with a grain of salt, but this looks > authoritative. > Well, Scaliger's father was apparently named after Julius Caesar (his full name was Julius Caesar Scaliger) so I suppose the roman emperor is the origin of the name either way - it just depends on how many pointers you have to go through to get there D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
D. Richard Hipp wrote: > > See http://en.wikipedia.org/wiki/Julian_day > > Note that "Julian" in Julian Day Number and Julian Calendar > refer to two different people named Julius. The Julian Day Number > Julian is Julius Scaliger, the father of the guy who invented > the julian day number in 1583. Julian in Julian Calendar refers to > Julius Caesar, the Roman emperor. > Richard, From the wikipedia article you cited: Note: although many references say that the Julian in "Julian day" refers to Scaliger's father, Julius Scaliger, in the introduction to Book V of his Opus de Emendatione Temporum ("Work on the Emendation of Time") he states, "Iulianum vocavimus: quia ad annum Iulianum dumtaxat accomodata est", which translates more or less as "We have called it Julian merely because it is accommodated to the Julian year." This Julian refers to Julius Caesar, who introduced the Julian calendar in 46 BC. I can't vouch for the veracity of this note, but he he seems to know what he is talking about and has given what is purported to be a reference from the original author that backs his claim (as best I can tell from the quoted Latin and its translation). As always you have to take everything on wikipedia with a grain of salt, but this looks authoritative. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
On Apr 6, 2008, at 5:12 PM, John Stanton wrote: > Not a hack, but the traditional way to store dates and times. Sqlite > functions use a magic epoch which facilitates the presentation of the > date and time in the form of the major calendars. > > We do not use the Julian calendar these days. It was supplanted by > the > Gregorian in 1582. Julian refers to defining a date by offset from an > epoch and is the preferred method of storing date and time. > See http://en.wikipedia.org/wiki/Julian_day Note that "Julian" in Julian Day Number and Julian Calendar refer to two different people named Julius. The Julian Day Number Julian is Julius Scaliger, the father of the guy who invented the julian day number in 1583. Julian in Julian Calendar refers to Julius Caesar, the Roman emperor. The date and time routines in SQLite use the Gregorian calendar. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
Not a hack, but the traditional way to store dates and times. Sqlite functions use a magic epoch which facilitates the presentation of the date and time in the form of the major calendars. We do not use the Julian calendar these days. It was supplanted by the Gregorian in 1582. Julian refers to defining a date by offset from an epoch and is the preferred method of storing date and time. sqlfan wrote: > 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 >> >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
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. There is a set of embedded functions which implement dates and times using 64 bit floating point numbers. In applications requiring it we have declared types DATE, TIMAE and DATEIME. These are intercepted in a wrapper and implement date and time processing. Alternatively you can use regular ISO1806 date and time formaats as TEXT or use the floaring point and handle dates and times in your application. Sqlite gives you a lot of options when embedding date and time storage and prcessing. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
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, -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 -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, -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, -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
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
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
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
[sqlite] What is the standard way to store dates and do operations with dates please?
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. -- View this message in context: http://www.nabble.com/What-is-the-standard-way-to-store-dates-and-do-operations-with-dates-please--tp16514369p16514369.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