Jump to content


SQL Query Works But Date is exporting as an integer..

sql query

  • Please log in to reply
3 replies to this topic

#1 shifts



  • Joined: 27-October 03

Posted 23 September 2012 - 17:39

Hello World ;-)

I;ve been looking at extracting messages from my iPhone without any real special software (aka free)

So far I've got the messages out, extracted into a CSV file but the same stamp I'm lost on

This is the command I'm running

select message.rowid, date, handle.id, message.text from message, handle where message.handle_id=handle.rowid

In the DB table date is stored as an integer.

So is there a way to run the query but output the date as a proper time and date format?

Thanking you in advance.

#2 AnthonySterling


    Offering bad advice since 23-December 04.

  • Joined: 23-December 04
  • Location: North-East, UK

Posted 23 September 2012 - 17:57

What type of database is it? It sounds like the integer could be a UNIX timestamp, check the documentation for guidance on how to convert this.

#3 OP shifts



  • Joined: 27-October 03

Posted 23 September 2012 - 18:03

It's an SQL Lite db. Basically it's the DB format of what the iPhone uses to store texts, imessages in etc. I'll see if I can find a link to it's schematics.

My DB skills only go as far as that query above which i cobbled together from my memories of SQL in college some 16 years ago :D

#4 simplezz


    Neowinian Senior

  • Tech Issues Solved: 8
  • Joined: 01-February 12

Posted 23 September 2012 - 18:11

I store dates in julian format in my sqlite databases, so it's probably something similar. There are usually conversion functions in various programming languages. Sqlite has some built-in date/time functions . Maybe they'll do what you need.