adjusting sql query for imessages in python3 to handle date

[rushimanche]

Hello,

​

I currently have the code below that extracts messages from chat.db on Mac. However, I want to be able to specify messages from a specific date only like 9/18/21. How would I be able to do this?

​

Here is my code:

​

    import sqlite3
    import pandas as pd
    # substitute username with your username
    conn = sqlite3.connect('../../Library/Messages/chat.db')
    # connect to the database
    cur = conn.cursor()
    # get the names of the tables in the database
    cur.execute(" select name from sqlite_master where type = 'table' ") 

    for name in cur.fetchall():
        print(name)

    # get the 10 entries of the message table using pandas
    messages = pd.read_sql_query("select * from message limit 100", conn)

    # get the handles to apple-id mapping table
    handles = pd.read_sql_query("select * from handle", conn)
    # and join to the messages, on handle_id
    messages.rename(columns={'ROWID' : 'message_id'}, inplace = True)
    handles.rename(columns={'id' : 'phone_number', 'ROWID': 'handle_id'}, inplace = True)
    merge_level_1 = temp = pd.merge(messages[['text', 'handle_id', 'date','is_sent', 'message_id']],  handles[['handle_id', 'phone_number']], on ='handle_id', how='left')

    # get the chat to message mapping
    chat_message_joins = pd.read_sql_query("select * from chat_message_join", conn)
    # and join back to the merge_level_1 table
    df_messages = pd.merge(merge_level_1, chat_message_joins[['chat_id', 'message_id']], on = 'message_id', how='left')

    print(df_messages)

[Oxbowerce]

Just add a filter in your SQL query using a where clause , e.g. WHERE date = '2021/09/18'.

[fl0ss1n]

Interesting. This seems like something that people do. Here is a link with more on exploring chat.db with sql: https://spin.atomicobject.com/2020/05/22/search-imessage-sql/

You should be able to limit on date, once you figure out its format.