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 'YYYY-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 ('YYYY-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.
YYYYMMDDHHMMSS 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 YYYYMMDDHHMMSS 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, YYYYMMDD
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.
