Re: [sqlite] What is the standard way to store dates and do operations with dates please?

2008-04-09 Thread Florian Weimer
* 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?

2008-04-07 Thread D. Richard Hipp

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?

2008-04-07 Thread Dennis Cote
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?

2008-04-06 Thread D. Richard Hipp

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?

2008-04-06 Thread John Stanton
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?

2008-04-06 Thread John Stanton
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?

2008-04-05 Thread Clark Christensen
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?

2008-04-05 Thread sqlfan

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?

2008-04-05 Thread Dennis Cote
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?

2008-04-05 Thread sqlfan

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