So just to sanity check my understanding of Julian dates I thought I'd post here to get confirmation. Turns out SQLite uses Julian dates internally to store timestamps, dates, etc. Often times when working with any database I've gotten in the habit of storing dates in UTC regardless local time since I can figure out localtime easily form this, and it neutralize time issues in distributed systems. Plus most databases just store a timestamp as you give it, and it doesn't have a timezone so your left to handle that on your own. The pros of this approach is dates can be compared against each other regardless of where they were created since they're always in UTC.
Since I have to use Julian dates are they independent of timezone? I believe they are so there's no need to store them any different? No need to convert between utc and localtime before adding them as Julian to the database. Does anyone know the precision of these numbers in SQLite? I'm seeing some of my times being off by quite a bit (500+ms) in my unit tests. I've set some of these columns as default CURRENT_TIMESTAMP and I see skew from the local system time. It could be that there is time elapsing between when I grab current time and when it makes it to the database, but it's very strange and arbitrary. For example: var now : Date = new Date(); dao.insert( new SomeObject() ); var dbObject : SomeObject = dao.find( ... ); // assuming created_at is a timestamp in the database that defaults to CURRENT_TIMESTAMP assertEquals( now.time, dbObject.created_at.time ); That assertEquals will fail somethings by being off as much as 500ms or more. Charlie
