Results 1 to 3 of 3
  1. #1
    Anyone familiar with SQLite databases - specifically how date/time is encoded?
    cwa107's Avatar
    Member Since
    Dec 20, 2006
    Location
    Lake Mary, Florida
    Posts
    26,758
    Specs:
    15" MBP, Core i7/2GHz, 8GB RAM, 480GB Crucial M500 SSD
    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?
    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!

    https://youtu.be/KHZ8ek-6ccc

  2. #2
    Anyone familiar with SQLite databases - specifically how date/time is encoded?
    vansmith's Avatar
    Member Since
    Oct 19, 2008
    Location
    Toronto
    Posts
    19,398
    Specs:
    2012 13" MBP (2.5 i5, 8GB)
    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

  3. #3
    Anyone familiar with SQLite databases - specifically how date/time is encoded?
    cwa107's Avatar
    Member Since
    Dec 20, 2006
    Location
    Lake Mary, Florida
    Posts
    26,758
    Specs:
    15" MBP, Core i7/2GHz, 8GB RAM, 480GB Crucial M500 SSD
    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!

    https://youtu.be/KHZ8ek-6ccc

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Date and Time?
    By klm46 in forum OS X - Operating System
    Replies: 3
    Last Post: 05-17-2009, 12:32 PM
  2. Date and Time
    By skimonster in forum OS X - Operating System
    Replies: 2
    Last Post: 05-16-2009, 08:41 PM
  3. Set "last modified date to IPCT date/time?
    By mercator in forum Images, Graphic Design, and Digital Photography
    Replies: 0
    Last Post: 02-21-2009, 03:01 AM
  4. Date & Time in preferences different to terminal time
    By surveyance in forum OS X - Operating System
    Replies: 0
    Last Post: 08-21-2008, 07:44 PM
  5. Printer TIME/DATE Need Help
    By zer0cach3 in forum OS X - Operating System
    Replies: 0
    Last Post: 02-12-2006, 07:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •