Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
Thanks for insight! An offset from 2001 works does not suits record from 2013-09-03 - an offset is 2000-10-25 04:00:00. I wonder if there are any rationales behind using timestamps like that... –  Ice Code Nov 18 '13 at 9:25
add comment

Thank you, CL.! I believe the following query solves my conversion issue:

(strftime('%s', '2013-11-19', 'localtime', 'start of day') - strftime('%s', '2001-01-01 04:00:00'))

My workaround would be to generate insert transactions in excel or notepad++, insert them into DB and run an update query to update all transactions from '2013-11-19' format to internally usable by the app.

In case I consider to switch from Money IQ I would also update 'ztitle' field with full timestamp for just in case.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.