[SOLVED] thunderbird sqlite backup file. converting dates

schneidz · 07-11-2015, 07:10 PM

hi, i have this sqlite backup file:
./.thunderbird/xycahv7k.default/calendar-data/local.sqlite

i can get this data:

Code:

sqlite> select title, event_start from cal_events where id = '21a690b7-ba53-43e5-9d85-8b3805bceef1';
title|event_start
mom's b-day|1332979200000000

how do i convert the event_start to mm/dd/yyyy (either in sql or bash).

this is the error i am getting:

Code:

[schneidz@hyper schneidz]$ date 1332979200000000
date: invalid date ‘1332979200000000’

schneidz · 07-11-2015, 07:16 PM

this is the command

Code:

[schneidz@hyper schneidz]$ date -u --date='@1348531200000000'
Tue Jul 26 00:00:00 UTC 42735222
[schneidz@hyper schneidz]$ date -u --date='@1348531200'
Tue Sep 25 00:00:00 UTC 2012

i had to remove the trailing 0s until i got the correct date.

Kroggen · 11-30-2015, 10:00 PM

In SQLite you can use the datetime function:

SELECT datetime(1092941466, 'unixepoch');

will return the julian day number converted to a date in the "yyyy-mm-dd hh:mm:ss" format, in UTC.

To convert to localtime:

SELECT datetime(1092941466, 'unixepoch', 'localtime');

In your query:

select title, datetime(event_start, 'unixepoch') as ev_start from cal_events where id = '21a690b7-ba53-43e5-9d85-8b3805bceef1';