On 10/21/2008 05:23 AM, Doug wrote: > In an effort to share some hard-won insight: > > I used to store local times in the database, but it's a bad idea and I got > bit. Think about what gets stored: A number of seconds/nanoseconds/whatever > from some time in the past (Jan 1, 1970 for example). When daylight savings > arrives, all of your stored values are now off by an hour (and if they're > around midnight, they're on the wrong date too). And times you save now > will similarly be off when you leave day light savings. It's much better to > store UTC and then convert to local time as needed. > > I (and some customers) went through a lot of pain as I switched everything > to UTC -- life has been good since then.
I complete agree with Doug here said. In my years of programming it's ALWAYS a good idea to *store* dates in UTC, and convert them to your timezone when you *display* them. Otherwise you'll always have issues of "this date was when DST was in effect" and this date was "when DST didn't count" so you're always trying to compensate. DON'T DO THAT! Let SQLite do that work for you by storing UTC and doing the convert in your select statement. It will be 100x more accurate. Depending on what you're doing with the dates, I almost always store dates in Unixtime, as they're much easier to work with than a string date value. SQLite works flawlessly with unixtime values also. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
