webOS Nation Forums >  webOS Devices >  Palm Pre and Pre Plus > Deciphering SMS timestamps from download
Deciphering SMS timestamps from download
Thread Tools Display Modes
Old 06/25/2012, 07:41 AM   #1 (permalink)
Posts: 16
I just used save/restore in order to save all the text msgs on my Pre's. Now, I just need to figure out how to decipher the timestamps. I'm sure its some commonly known thing that I have no clue about...

but anyway, there are two fields listed: timestamp and devicetimestamp. They both appear to use the same format.

example: 1340405637353, which should be about 1754 on 6/22/12.

Anyone out there know how to decipher these?

SkiMedicKC is offline   Reply With Quote
Old 06/25/2012, 09:23 AM   #2 (permalink)
Armada's Avatar
Posts: 39
It might be a Unix style timestamp. Try to convert here Epoch Converter - Unix Timestamp Converter
Armada is offline   Reply With Quote
Old 06/25/2012, 10:17 AM   #3 (permalink)
Posts: 16
Originally Posted by beveragevendor View Post
It might be a Unix style timestamp. Try to convert here Epoch Converter - Unix Timestamp Converter
That's exactly what it is...thanks for the quick response. Works perfectly. Now, if I can just teach excel to convert it somehow.
SkiMedicKC is offline   Reply With Quote
Old 06/25/2012, 11:59 AM   #4 (permalink)
Posts: 351
I believe the Unix timestamp is one-thousandths of a second since 1/1/1970, which should be relatively easy to do in Excel or something else.

1340405637353 /1000 = 1340405637.353 seconds = 22340093.956 minutes = 372334.899 hours = 15513.954 days = ( 1/1/1970 + 42 years + 173.xxx days ) = approx. 6/21/2012 - 6/22/2012.

I may have made a one-day mistake in there somewhere when I tried to manually account for leap years since 1970, and I think it's stored as GMT instead of local time, but I think that gives you enough to go on.
ALuckyGuy is offline   Reply With Quote
Old 02/09/2013, 04:40 PM   #5 (permalink)
Posts: 1
Hi Folks,

I am a total newbie to the whole world of OS. I have no idea how to write code or do all the advanced things that folks on this site can do. I am, however, a quick learner and I need some help.

I used to use a Palm Pre. I now use the EVO Design. I am trying to get 5000 texts between me and my, now, husband off of the Pre to give to him for Valentine's Day. I have gotten as far as extracting the db3s from the Pre and being able to view the texts in the FolderEntry table in SQLite and exporting that table to CSV to view in MS Excel. However - when I open the CSV File in Excel, it dumps ALL the info in to the 'A' column instead of spreading it across A/B/C, etc. Does anyone know how to get it to spread them?
SQLite will not let me select one column to copy/paste to another program. I am thinking my only option is to modify the entire table in SQL to contain only one column and export that to CSV and then opening it with MS Excel. By repeating the process and saving multiple XLSs that contain one table each, I can merge them into multiple columns in one xls file. This is a royal pain in the rear. As skimedickc said, there only appear to be a few columns in the messaging table that have usable info, i.e. the timestamps and the messages. If you know an easier way to extract pertinent info from the db3, PLEASE tell me!

Some random questions:
Is it accurate that when I opened the FolderEntry table, the only things really in there were timestamps and messages with phone numbers as ids? There are no actual dates, names, etc., in the fields correct? Just want to make sure I got a clean extraction of the whole db3 and didn't lose info somehow. Just seem to be a lot of empty fields in that table and an unnecssary waste of space on it.

Would it be constructive to run a compression on the db3? Backing it up first, of course... Will that get rid of all the indexes that have no info listed?

skimedickc: How did you do a save/restore?

Comment: The EpochConverter that Armada listed has a link on the page where you can enter 500 Unix timestamps at a time for conversion. I plan to copy/pasteintotheink/convert/repaste my timestamps into my excel file table so that I have the exact conversion and don't have to mess with excel formulas or figuring out when leap year happened...

Sorry I am so green. I loved my Pre, but I also love my new EVO Design. I am hoping that getting the texts off of my EVO and onto the computer is an easier process. If you know how to do that also, please let me know!

Thanks in advance for your replies/help!!!
Ladymaestro1 is offline   Reply With Quote
Old 02/09/2013, 09:44 PM   #6 (permalink)
GMMan's Avatar
Posts: 2,136
In Excel you can import data using different separators than commas by going to Data->Get External Data From Text.
Contact: @GMMan_BZFlag (me on Twitter)
webOS Releases: Change your App Catalog country: TouchPad/PC | TouchPad/webOS Resources | Search suggestion patch for browser | Cycling Email Notifications | Don't Doctor! Make a good support request. | How to post logs | webOS Charge Monitor
GMMan is offline   Reply With Quote


Thread Tools
Display Modes


Content Relevant URLs by vBSEO 3.6.0