解析“日期".备份中iPhone SMS文件的字段 [英] Parsing "date" field of iPhone SMS file from backup

问题描述

虽然这本身不是编程问题，但却是相关的.

While this isn't a programming question per se, it IS related.

因此，我试图弄清楚如何解析从iPhone备份的SMS DB.我正在查看邮件"表，特别是日期"字段.我注意到最近的邮件正在使用不同的编号系统来指示日期/时间.我将其范围缩小到切换到iMessage，因为我在1318470904发送了一条消息，并在340164736发送了答复.我知道这些消息的发送间隔不到一个小时，但它们表示>相差30年.

So I'm trying to figure out how to parse the SMS DB that gets backed up from the iPhone. I'm looking at the "messages" table, specifically the "date" field. I noticed that the more recent messages are using a different numbering system to indicate the date/time. I've narrowed it down to the switch to iMessage, as I have a message sent at 1318470904, with a reply sent at 340164736. I know for a fact that these messages were sent less than an hour apart, yet they're indicating > 30 years' difference.

任何人都知道如何使用此较新的系统准确地计算日期吗?是使用不同的纪元还是我需要做一些疯狂的数学运算?

Anybody know how to accurately calculate the date using this newer system? Is it using a different epoch or is there some crazy math I need to do?

最近的消息也会受到影响.文本(绿色气泡)以正常设置的日期存储，而通过iMe​​ssage(蓝色气泡)存储的任何内容均以不同的日期表示形式存储.

Recent messages are affected as well. Texts (green bubbles) are stored with the date set normally, and anything through iMessage (blue bubbles) is stored with the different date representation.

推荐答案

由于备份已导出为SQLite数据库格式，因此以下是在SQLite中将数字转换为实际日期的方法:

Since the backup is exported to SQLite database format, here's how to convert the number to a real date in SQLite:

select
    datetime(date + strftime('%s', '2001-01-01 00:00:00'),
       'unixepoch', 'localtime') as date,
    *
from message

这篇关于解析“日期".备份中iPhone SMS文件的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！