Technically neither the Georgian or Julian system takes into account the
timezone effect. That system was developed independently by the British
and French to determine the "local" time in parts of their empires.
SQLite is probably using Julian for the local machine's time, not a big
deal to convert it. It would depend if you need UTC or not. I have no
idea about the precision of SQLite. Julian can be very precise (just a
pain to read at times) so that shouldn't be the problem.
John
[email protected]
Charlie Hubbard wrote:
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
-------------------------------------------------------------
To unsubscribe from this list, simply email the list with unsubscribe in the
subject line
For more info, see http://www.affug.com
Archive @ http://www.mail-archive.com/discussion%40affug.com/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
