Dana Diederich wrote:
Dana,This is a related but different question/comment.In the six or so years of using MySQL, my various teams have never used anything except an unsigned INT and UNIX Epoch time to record timestamps. The given is, of course, we have always been a Perl/C/C++/UNIX shop, and we will be for a long time, at least. :-) I have looked at the growing list of MySQL date/time functions from time to time, and have always come to the conclusion that Epoch time is the best way for us to store date/time. Another given is that we don't need to save sub-second time stamps. I won't go into the pros of Epoch time. I'll just say that we've never found it wanting. I guess the cost is having queries like "where ts < (other_ts - (3600 * 24 * 7))", etc. Perl, our primary language, makes at an absolute breeze to convert to/from Epoch time. Can anyone share and/or comment about the use of Epoch time, especially the hazards? I'm asking because I want to make sure that I haven't built a comfortable little box that un-necessarily excludes some useful functions. :-)
I think you are pretty much right about storing epoch timestamps. They come in handy especially when you need to do many queries with time-based where clauses: indexes help you a lot and IMHO are very fast on integer columns, while they tend to be inefficient on datetime (&friends) columns [just think about ...WHERE TO_DAYS(when)-6 < TO_DAYS(NOW()) ]. If you use a scripting environment (say Perl), where you use your own programming logic rather than that of the database, I can't think of any drawbacks of using the epoch. Well, maybe one thing: if you "manually" browser your database, a ten character integer is not very talkative, while a datetime is. So, it depends: I would say that only use datetime in rarely used small tables, or where timestamps are only secondary data, or where you do not want to bother with epoch<->date conversions.
Oh and one more thing I am not really sure of: sometimes defining the day as 3600*24 or the year as 3600*24*365 is not the best idea... just think about leap years. How do you handle that? And there are some more "artifacts" in the Gregorian calendar, too...
- Cs.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
