Are Timestamp correct?

[kurisutofu]

Hi,

I couldn't use the application yet as I'm not on my Mac but I have extracted the database file from my iPhone and took a look at it with a sqlite database browser.

I could see the timestamp is not standard and after checking the source code here, I could see the comment in the code saying Apple seems to start from January 2001 instead of the unix January 1970.

In the sql query, I used the same addition than in the source code (31_365.25_24_60_60) but the results seem to be off a little (a few hours).

Going to the website http://calendarhome.com/, I got that between January 1970 and January 2001, there are 978307200 seconds and so I added those instead.

Now I seem to get something closer to reality for the timestamp.

Do you people who used the app get correct timestamps?

[cviebrock]

I noticed this too. The formula in the code equates to 978285600. Using MySQL to figure out the right number gives me:

mysql> select unix_timestamp('2001-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('2001-01-01 00:00:00') |
+---------------------------------------+
|                             978325200 |
+---------------------------------------+

That's 6 hours difference. Using sqllite3 gives a different answer though, 11 hours different:

sqlite> select (julianday('2001-01-01') - julianday('1970-01-01')) * 86400;
978307200.0

I recompiled the binary with both values, changed the code so it bucketed the data points by day instead of week, and it still seems to be 1 day off (comparing to my calendar so I know where I was on a particular day).

I suppose the way to tell for sure (apart from asking Apple) is to look at the data on a phone where the exact activation time is known. Thoughts?

[jonpearce]

My timestamps (as displayed on iPhoneTracker) are off by a few years! Places dated in 2010 actually should be 2006!

[tmcw]

See the FAQ.

The timestamp shows the time in seconds since January 1st 2001.

[petewarden]

It's very possible our displayed timestamps are off by a few hours, since I used that quick approximation in the code. I'll update it to your value in the source, thanks.

On Wed, Apr 20, 2011 at 8:03 PM, kurisutofu < reply@reply.github.com>wrote:

Hi,

I couldn't use the application yet as I'm not on my Mac but I have extracted the database file from my iPhone and took a look at it with a sqlite database browser.

I could see the timestamp is not standard and after checking the source code here, I could see the comment in the code saying Apple seems to start from January 2001 instead of the unix January 1970.

In the sql query, I used the same addition than in the source code (31_365.25_24_60_60) but the results seem to be off a little (a few hours).

Going to the website http://calendarhome.com/, I got that between January 1970 and January 2001, there are 978307200 seconds and so I added those instead.

Now I seem to get something closer to reality for the timestamp.

Do you people who used the app get correct timestamps?

Reply to this email directly or view it on GitHub: https://github.com/petewarden/iPhoneTracker/issues/13

[pitpalme]

No need to use any approximation or self defined constant ... I was curious what the exact, time zone neutral, and constant value might be and figured there already is a constant defined "NSTimeIntervalSince1970".

So "iOSToUnixOffset" can be removed and unixTimestamp calculated like this:

const float unixTimestamp = (timestamp+NSTimeIntervalSince1970);

To even simplify the code one could replace

const float timestamp = [timestamp_number floatValue];

with

const float unixTimestamp = [[NSDate dateWithTimeIntervalSinceReferenceDate:[timestamp_number floatValue]] timeIntervalSince1970];

deleting "iOSToUnixOffset" and later "unixTimestamp" definition. But I have not idea if this heavy "NSDate" instantiation and initialization affect performance significantly. I'd think so but haven't measured yet.

[kurisutofu]

Could someone do a print of NSTimeIntervalSince1970's value please? Would be useful to know the exact number, to use on sql queries or windows version of the software ;-)

[pitpalme]

Apple documentation at

https://developer.apple.com/library/mac/#documentation/Cocoa/Reference/Foundation/Classes/NSDate_Class/Reference/Reference.html#//apple_ref/doc/c_ref/NSTimeIntervalSince1970

states

#define NSTimeIntervalSince1970  978307200.0

Programmatically using

[NSString stringWithFormat:@"%f", NSTimeIntervalSince1970]

gives

978307200.000000

HTH.

[kurisutofu]

ok Thanks!