I'm a Thunderbird user, and I'm using the self-contained .sqlite file (global-messages-db.sqlite) to develop some sql scripts, I'm trying to make sense of the different objects inside this database, and there's the message table which has the column date but it does not seem as a date string, it shows me a bunch of numbers.
So far I have this query:
SELECT a.c1subject AS "SUBJECT",
a.c3author AS "FROM",
a.c4recipients AS "TO",
b.date,
c.folderURI AS "FOLDER_LOCATION",
c.name AS "FOLDER_NAME"
FROM messagesText_content a
INNER JOIN messages b ON a.docid=b.id
INNER JOIN folderLocations c ON b.folderID=c.id
WHERE a.c1subject LIKE ('Chunk of text%');
Here's the date result retrieve by the query:
1547902246000000
1547823370000000
1547651729000000
What could be the meaning of this? could this be because some configuration? how can I convert this string to a human readable date?
Note: I also used sqllite3 command directly from the command line to check this row and I have the same result.
any advice is appreciated, thanks.
UPDATE: in order to convert the epoch format to a human readable date I used this query on my SQLite database with the STRFTIME function:
SELECT a.c1subject AS "SUBJECT", STRFTIME('%d/%m/%Y %H:%M:%S',DATETIME(SUBSTR(b.date,1,10)-18000, 'unixepoch')) AS "DATE"
FROM messagesText_content a, messages b, folderLocations c
WHERE STRFTIME('%d/%m/%Y',DATETIME(SUBSTR(b.date,1,10), 'unixepoch')) > 'DD/MM/YYYY'
AND a.docid=b.id
AND b.folderID=c.id
AND a.c1subject = ('YOUR_TEXT)')
ORDER BY b.date DESC;
The -18000 parameter is used to substract 5 hours, because a particular server configuration.
Hope this helps!
The date is likely in seconds since epoch. There should be many sites that should help you convert it. For example, you can try this site to convert your dates: