Until your database/code get moved to a machine with a different epoch.....
g_White | [reply] |
i'm actually saving dates as a unix timstamp AND a mysql date in 2 seperate columns.
why?
its handy having the unix timestamp right there... but you can't easily take care of mysql date functions with it...
kept as a mysql date, mysql can spit out date functions faster and easier in 1 sql statement | [reply] |
This is a bit dangerous isn't it? What happens when a process accidentally updates one of the columns and not the other?
It would be safer/easier to write a function (either in perl or the db) to convert between the two formats, and only store one of them. I've not used mysql much, but unix_timestamp() might be useful there.
Regarding the original problem - getting thousands of lines into a project before discovering bugs like this is a good example of why writing tests as you go (or before you go in some cases) is such a sanity-saver. :)
| [reply] |
having the diff. date formats is a little dangerous indeed -- but thats my little 'sanity saver' test. i ended up killing the unix timestamp format, as i found mysql's datetime to be faster for my needs
at least on my operations, getting all the info for a 'day' was much faster matching the left 10 characters in mysql to a 'yyyy-mm-dd' date than was searching where 'timestamp > daystart AND timestamp < dayend'
to insert a date, i just do a 'FROM_UNIXTIME(time)' - and mysql could either spit it out as a yyyy-mm-dd or TO_UNIXTIME
| [reply] |