Issues with SQLite timestamps

Any trouble you encounter with the Pedias, here's the place to ask for help.

Issues with SQLite timestamps

Postby d9pouces » Mon Oct 05, 2009 4:31 am

Hello,

I'm working on a small php tool to synchronize two pediadatabase, and I've a small problem with timestamps.
I'm currently using this SQL query : query("SELECT *,strftime('%s', ZDATEADDED), strftime('%s', ZDATEEDITED) FROM ZENTRY") to retrieve informations about entries. I need this timestamp to determine if a entry was modified after the last synchronization.

However, the timestamp stored in the ZDATEADDED and ZDATEEDITED fields are floating point numbers, and I don't know how to transform them into regular unix timestamps.
I tried to convert them with the sqlite strftime function, but it was unsuccessful :( 

Thus, could you kindly explain a better way to get a standard unix timestamp?
d9pouces
Captain
Captain
 
Posts: 20
Joined: Wed Aug 05, 2009 3:08 am

Re: Issues with SQLite timestamps

Postby Conor » Mon Oct 05, 2009 2:56 pm

The float is a Mac OS X timestamp. It's the number of seconds since January 1st, 2001. To translate to the UNIX timestamp, which is based on 1/1/1970, add 978328800 seconds.

It's also GMT based, so if you like to go further and get the correct local time use the following function: datetime(timestamp, 'localtime').
User avatar
Conor
Top Dog
 
Posts: 5273
Joined: Sat Jul 03, 2004 12:58 pm

Re: Issues with SQLite timestamps

Postby d9pouces » Tue Oct 06, 2009 2:41 pm

Thank you; now I've a fully fonctionnal synchronization tool :-)
d9pouces
Captain
Captain
 
Posts: 20
Joined: Wed Aug 05, 2009 3:08 am

Re: Issues with SQLite timestamps

Postby d9pouces » Tue Nov 10, 2009 5:50 pm

I've finished my synchronization tool (actually, in Python) for Bookpedia databases, but I've a new question :)

I somebody is interested, I could try to extend it to other pedias :)

I don't not understand the exact meaning of the Z_OPT field which can be found in the database...
Have I to synchronize it between two databases, or must I to left it unchanged ?
d9pouces
Captain
Captain
 
Posts: 20
Joined: Wed Aug 05, 2009 3:08 am

Re: Issues with SQLite timestamps

Postby Conor » Tue Nov 10, 2009 6:18 pm

Took a little digging and playing around but I figured out what the Z_OPT column is for, it's the number of changes that have been done to a row. So it would be a useful field in your synchronization tool to tell if a row has changes, although I would still recommend our personal dateEdited field. Core Data could change or stop the use of the Z_OPT column, since it's an internal variables (probably used to also detect when merges are necessary). Don't think that not synchronizing Z_OPT would be dangerous, but to be on the safe side I would take the higher of the values and synchronize that.
User avatar
Conor
Top Dog
 
Posts: 5273
Joined: Sat Jul 03, 2004 12:58 pm

Re: Issues with SQLite timestamps

Postby d9pouces » Mon Nov 16, 2009 4:53 am

Thank you for taking the time to answer :-)

Everything is now perfectly synchronized :-)
d9pouces
Captain
Captain
 
Posts: 20
Joined: Wed Aug 05, 2009 3:08 am


Return to Support

cron