How to guess or recover a timestamp conversion in sqllite

date formatrecoverysqlitetimestamp

I am in the middle of tiny forensic effort =)
I am trying to transfer transactions from Money IQ (IOS) to My Wallet+ (IOS) as both apps use sqlite. Money IQ uses a standard unixepoch timestamp. However, I got troubles with My Wallet+. I have matched actual dates with timestamps of sample transactions:

actual date | app timestamp
2013-09-03 => 399924000
2013-10-26 => 404424000
2013-11-04 => 405201600
2013-11-04 => 405201600
2013-11-10 => 405720000

My objective is convert '%Y-%m-%d %H:%S' to this format. I think the app does not store the time part of a timestamp thinking that transactions occurred at midnight. My best guesses are H1 for ts to date conversion and H2 for date to ts conversion (inaccurate by 14400):

H1: strftime('%Y-%m-%d %H:%S', datetime(zdate, 'unixepoch'), '31 years', 'localtime')
H2: strftime('%s', '2013-11-13', '-31 years')

Is there anything I'm missing or the H2 is the most appropriate way to convert? Thank you!

UPD: I have expanded dataset:

timestamp | unix epoch diff     | manual note
---------------------------------------------------
321829200 | 2001-01-01 03:00:00 | 2011-03-15
347054400 | 2001-01-01 04:00:00 | 2012-01-01
378676800 | 2001-01-01 04:00:00 | 2013-01-01
395092800 | 2001-01-01 04:00:00 | 2013-07-10
399924000 | 2000-12-31 06:00:00 | 2013-09-03
400881600 | 2001-01-01 04:00:00 | 2013-09-15
404424000 | 2001-01-01 04:00:00 | 2013-10-26
405201600 | 2001-01-01 04:00:00 | 2013-11-04
405201600 | 2001-01-01 04:00:00 | 2013-11-04
405720000 | 2001-01-01 20:53:00 | 2013-11-10 16:53
406411200 | 2001-01-01 16:00:00 | 2013-11-18 12:00

Best Answer

The Unix epoch timestamp is defined as the number of seconds since the beginning of 1970 (in UTC).

These timestamps are measured from the beginning of 2001, and appear to use the respective local time zone:

> select datetime(strftime('%s', '2013-11-10') - 405720000, 'unixepoch');
2001-01-01 04:00:00
> select strftime('%s', '2013-11-10') - strftime('%s', '2001-01-01 04:00:00');
405720000

To correct for the random time zone differences, you should round to the nearest midnight.

Related Question