Anyone familiar with SQLite databases - specifically how date/time is encoded?

So, I have what I think is a relatively simple problem, but unfortunately I know just enough about databases to be dangerous...

Anyway, onto the issue - I was using an app called 'Mileage' to monitor my fuel mileage on my vehicles. I've got about a year's worth of data stored in the app. Unfortunately, Mileage doesn't have an iOS port, so I'm working on getting my data manually imported into Gas Buddy. I forgot to export my data from 'Mileage' before I wiped the phone, but I did make a full backup of the filesystem. In that backup, I caught a .db file sitting in a folder called 'Mileage'. Looking at it with a hex editor, it appears to be a SQLite database.

I used a program called RazorSQL to export the main table into a standard .xls file and everything looks great and is totally useful except for the dates. From what I understand, SQLite databases typically use Julian dates, but the format of these dates looks a little longer - I'm guessing the time is encoded in each as well.

So, here's an example of my dates:

1274633744451
1275686675492
1276701619996
1277484810778
1278433842560
1279541530698
1280010258114
1281027036868
1282137286753
1282409065438
1283638377050
1284761292282
1285692796895
1286663125562
1287437900595
1288385108871
1289342008051
1290295594300
1291503590046
1292416287073
1293398612953
1294184497058
1295125736672
1295459339414
1296367827810

I've tried butting them up against a few Excel formulas, but no go. I don't need anything fancy, basically I'm just trying to get into a Gregorian format similar to MM/DD/YYYY HH:MM:SS.

Any ideas?

My first guess is Unix epoch time. I tried converting the last number there using this and it came up with "Sun Jan 30 2011 01:10:27 GMT-0500 (EST)". Does that look right?

I know that doesn't help you get them into dates (unless you want to put them in one at a time using that tool) but it should at least give you a sense of what those numbers are.

If I read this correctly, dates/times can indeed be stored as Unix time. My suggestion would be to try and convert a few to see if they match up.

vansmith said:

My first guess is Unix epoch time. I tried converting the last number there using this and it came up with "Sun Jan 30 2011 01:10:27 GMT-0500 (EST)". Does that look right?

Click to expand...

Wow. I think that's right. I'll check out the converter. Thanks Van, you are brilliant.