New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
OS X - Apps and Games Discussion of applications and games available for Mac OS X.

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


Post Reply New Thread Subscribe

 
Thread Tools
cwa107

 
cwa107's Avatar
 
Member Since: Dec 20, 2006
Location: Middletown, Pennsylvania
Posts: 26,493
cwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond repute
Mac Specs: 15" MBP, Core i7/2GHz, 8GB RAM, 480GB Crucial M500 SSD

cwa107 is offline
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?

Liquid and computers don't mix. It might seem simple, but we see an incredible amount of people post here about spills. Keep drinks and other liquids away from your expensive electronics!
QUOTE Thanks
vansmith

 
vansmith's Avatar
 
Member Since: Oct 19, 2008
Location: Toronto
Posts: 17,815
vansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond repute
Mac Specs: 2012 13" MBP (2.5 i5, 8GB)

vansmith is offline
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.

Important Links: Community Guidelines : Use the reputation system if you've been helped.
M-F Blog :: Write for the blog
Writing a Quality Post
QUOTE Thanks
cwa107

 
cwa107's Avatar
 
Member Since: Dec 20, 2006
Location: Middletown, Pennsylvania
Posts: 26,493
cwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond reputecwa107 has a reputation beyond repute
Mac Specs: 15" MBP, Core i7/2GHz, 8GB RAM, 480GB Crucial M500 SSD

cwa107 is offline
Quote:
Originally Posted by vansmith View Post
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?
Wow. I think that's right. I'll check out the converter. Thanks Van, you are brilliant.

Liquid and computers don't mix. It might seem simple, but we see an incredible amount of people post here about spills. Keep drinks and other liquids away from your expensive electronics!
QUOTE Thanks

Post Reply New Thread Subscribe


« iTunes - List/Grid view help! | Automator - how do I..... »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

All times are GMT -4. The time now is 06:21 AM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
X

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?