Re: [sqlite] Data/time storing?
> ... gave me enough fodder to waste a bit more time ... FWIW, the SQLite date/time functions were designed so that dates could be stored as astronomical julian-day numbers (floating point). That's the only form that gives millisec precision, but some other form may be more convenient for a particular application. Regards
Re: [sqlite] Data/time storing?
I have seen the possible format's sqlite seems to understand.
However MMDDTHH:MM:SS having the T is not prefereable to me.
This would be my favourite:
MMDD HH:MM:SS
Maybe a more recognizable fieldtype like "DATE", "DATE-TIME" or "TIME" might
help also.
It's better sqlite enforces a rule than we all come up with our custom
types.
Afaik there is no date fieldtype..
- Original Message -
From: "Henry Miller" <[EMAIL PROTECTED]>
To:
Sent: Monday, August 01, 2005 4:34 PM
Subject: Re: [sqlite] Data/time storing?
On 8/1/2005 at 08:41 Puneet Kishor wrote:
I am curious about this as well... not about the various functions,
but
what is, if at all, a better way to store the values -- as '-MM-DD
HH:MM:SS' strings (are they not stored internally as just strings?) or
as unixepoch time (which would likely be stored as an int)?
Is not the latter (unixepoch) faster than the former ('-MM-DD
HH:MM:SS' strings)?
Is one more malleable than the other for conversion into various other
display forms as well as for DATETIME calculations?
This depends on what you want. There is no best for all situations.
Either form is convertible to the other.
MMDDHHMMSS tends to be readable anyone, while epoch is not. This
often makes for simpler code for simple projects.However when you
need to manipulate time in your code it is much easier to do arithmetic
on epoch time. Common programing languages tend to have good
libraries for turning epoch time into something readable, but it is
more code than a simple print on MMDDHHMMSS time.Epoch will run
out of time in 2036 (2038? one of those two), which is creeping up fast
- many current programers will still be working then! (But 64 bit
platforms are coming fast, and that will solve this problem for our
lifetimes, while introducing many other problems)
If your field techs will use some tool to dump the database, MMDD
format is much better, as they can understand it. This is a large win
in many cases. Field debugging is often more expensive than programmer
coding, so if dates are useful in field debugging it can be worth the
pain of using this format in code in the long run. However this method
fails on daylight savings time if you are in the repeated hour and need
to know if it is the first or second.
Epoch is based on UTC, so and the built in libraries handle time zones,
leap years, daylight savings time, and sometimes leap seconds (there
may be more factors I can't recall).This is a hard problem to
solve, and the libraries were written by smarter people than you, and
are well debugged by now. Governments change the exact date or
daylight savings time fairly often, with epoch you don't have to worry
about updating your program for these new dates..
Remember, it is easy to convert between the two (so long as daylight
savings time isn't involved). There is no one best for everyone, so
quit looking for it! Remember business considerations are often bigger
than technical considerations. Sometimes a critical issue will be
subtile for years. (day light savings time for instance may force use
of epoch despite the cost in field debugging time)
Make a choice and move on. This is one of those issues where it is
fairly easy to understand all the concerns, so people like to debate it
in depth to prove they are paying attention. Doing so is a waste of
time.
Re: [sqlite] Data/time storing?
Henry Miller wrote:
On 8/1/2005 at 08:41 Puneet Kishor wrote:
I am curious about this as well... not about the various functions, but
what is, if at all, a better way to store the values -- as '-MM-DD
HH:MM:SS' strings (are they not stored internally as just strings?) or
as unixepoch time (which would likely be stored as an int)?
Is not the latter (unixepoch) faster than the former ('-MM-DD
HH:MM:SS' strings)?
Is one more malleable than the other for conversion into various other
display forms as well as for DATETIME calculations?
This depends on what you want. There is no best for all situations.
Either form is convertible to the other.
that, I knew.
and the libraries were written by smarter people than you,
;-). That I didn't.
Make a choice and move on. This is one of those issues where it is
fairly easy to understand all the concerns, so people like to debate it
in depth to prove they are paying attention. Doing so is a waste of
time.
well, you answered my question in depth, and gave me enough fodder to
waste a bit more time thinking about it now rather than regretting later.
Many thanks for your detailed reply.
--
"measure twice, cut once"
Re: [sqlite] Data/time storing?
On 8/1/2005 at 08:41 Puneet Kishor wrote:
>I am curious about this as well... not about the various functions,
but
>what is, if at all, a better way to store the values -- as '-MM-DD
>HH:MM:SS' strings (are they not stored internally as just strings?) or
>as unixepoch time (which would likely be stored as an int)?
>
>Is not the latter (unixepoch) faster than the former ('-MM-DD
>HH:MM:SS' strings)?
>
>Is one more malleable than the other for conversion into various other
>display forms as well as for DATETIME calculations?
>
This depends on what you want. There is no best for all situations.
Either form is convertible to the other.
MMDDHHMMSS tends to be readable anyone, while epoch is not. This
often makes for simpler code for simple projects.However when you
need to manipulate time in your code it is much easier to do arithmetic
on epoch time. Common programing languages tend to have good
libraries for turning epoch time into something readable, but it is
more code than a simple print on MMDDHHMMSS time.Epoch will run
out of time in 2036 (2038? one of those two), which is creeping up fast
- many current programers will still be working then! (But 64 bit
platforms are coming fast, and that will solve this problem for our
lifetimes, while introducing many other problems)
If your field techs will use some tool to dump the database, MMDD
format is much better, as they can understand it. This is a large win
in many cases. Field debugging is often more expensive than programmer
coding, so if dates are useful in field debugging it can be worth the
pain of using this format in code in the long run. However this method
fails on daylight savings time if you are in the repeated hour and need
to know if it is the first or second.
Epoch is based on UTC, so and the built in libraries handle time zones,
leap years, daylight savings time, and sometimes leap seconds (there
may be more factors I can't recall).This is a hard problem to
solve, and the libraries were written by smarter people than you, and
are well debugged by now. Governments change the exact date or
daylight savings time fairly often, with epoch you don't have to worry
about updating your program for these new dates..
Remember, it is easy to convert between the two (so long as daylight
savings time isn't involved). There is no one best for everyone, so
quit looking for it! Remember business considerations are often bigger
than technical considerations. Sometimes a critical issue will be
subtile for years. (day light savings time for instance may force use
of epoch despite the cost in field debugging time)
Make a choice and move on. This is one of those issues where it is
fairly easy to understand all the concerns, so people like to debate it
in depth to prove they are paying attention. Doing so is a waste of
time.
Re: [sqlite] Data/time storing?
On Aug 1, 2005, at 7:57 AM, Griggs, Donald wrote:
Re:
Wonder what the best way is to store a date (and time).
Hello Edwin,
If you haven't already, you'll want to read about the sqlite date/time
functions at:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
I am curious about this as well... not about the various functions, but
what is, if at all, a better way to store the values -- as '-MM-DD
HH:MM:SS' strings (are they not stored internally as just strings?) or
as unixepoch time (which would likely be stored as an int)?
Is not the latter (unixepoch) faster than the former ('-MM-DD
HH:MM:SS' strings)?
Is one more malleable than the other for conversion into various other
display forms as well as for DATETIME calculations?
--
Puneet Kishor
RE: [sqlite] Data/time storing?
Re: Wonder what the best way is to store a date (and time). Hello Edwin, If you haven't already, you'll want to read about the sqlite date/time functions at: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors. -Original Message- From: Edwin Knoppert [mailto:[EMAIL PROTECTED] ...
[sqlite] Data/time storing?
Wonder what the best way is to store a date (and time). At this time i use "mmdd" but is there a by sqlite recommended method? This aplies time as well, not sure what i'll use, prob HH:MM:SS. But then it lacks ms's.
