Issues with SQLite timestamps

Any trouble you encounter with the Pedias, here's the place to ask for help.
Post Reply
d9pouces
Captain
Captain
Posts: 20
Joined: Wed Aug 05, 2009 3:08 am

Issues with SQLite timestamps

Post by d9pouces »

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?
User avatar
Conor
Top Dog
Posts: 5345
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Issues with SQLite timestamps

Post by Conor »

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').
d9pouces
Captain
Captain
Posts: 20
Joined: Wed Aug 05, 2009 3:08 am

Re: Issues with SQLite timestamps

Post by d9pouces »

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

Post by d9pouces »

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 ?
User avatar
Conor
Top Dog
Posts: 5345
Joined: Sat Jul 03, 2004 12:58 pm
Contact:

Re: Issues with SQLite timestamps

Post by Conor »

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.
d9pouces
Captain
Captain
Posts: 20
Joined: Wed Aug 05, 2009 3:08 am

Re: Issues with SQLite timestamps

Post by d9pouces »

Thank you for taking the time to answer :-)

Everything is now perfectly synchronized :-)
Post Reply