Get calendar events using Sqlite - pleased with myself

[Manners]

I saw this article: http://www.pocketables.com/2012/07/tasker-google-calendar-event-announcer-task-download.html about Tasker and calendar events.  I wanted to use Tasker to announce my calendar events but, as my other posts have noted, I'm not a fan of constantly grabbing data from HTTP get and chopping up with variable split, namely because of the added layer of connecting to a web server and all the issues that can crop up with that.  Also my issue is/was I have calendars in my phone that don't come from Google - like my work calendar.  So I started digging around the calendar database on the phone and pleasingly found the data.

After a quick self taught crash course in sqlite3 (which is so pleasant to use) I found a clean, efficient way to grab exactly the data you want from one Tasker action:

- Run Shell

- Command:

              sqlite3/data/data/com.android.providers.calendar/databases/calendar.db

              SELECT begin, calendar_displayname, title FROM Instances, Events, Calendars WHERE Instances.event_id = Events._id AND 

              date(datetime(begin / 1000 , 'unixepoch')) = date('now') AND Events.Calendar_id = Calendars._id ORDER BY Instances.begin ASC;

- Save Output in a variable

- Chop up variable into the right bits.  The cool thing about this is that sqlite will output your events nicely, with just a "|" to divide up fields - no unnecessary characters.

This is quick and works very well.  The key for anyone trying to use this is to nail down your sqlite query to return just what you want and in the order you want it.  Although this isn't imperative as you can always just chop up the outcome with variable split.  Note my sqlite query here returns the days events with the earliest first.

The app sqlite debugger: https://play.google.com/store/apps/details?id=oliver.ehrenmueller.dbadmin&feature=search_result#?t=W251bGwsMSwyLDEsIm9saXZlci5laHJlbm11ZWxsZXIuZGJhZG1pbiJd is really useful to help you build your queries.  

Note you may have to be rooted for this to work.

You may need to install sqlite3 into bin if your android version deosn't already have it.

I'm excited by the possibilities of poking databases in this way from Tasker.  Android stores a lot of settings and useful references in these databases and sqlite lets you update values too - for example I can directly change the folder of the Photo Table daydream in the Settings.db using this concept.

[TomL]

That's a great writeup!

If you're keen on learning more sql, here's a few tips.

Sqlite3 can do a lot with strings, so you can do something like:

select substr(colA, 2, 6)||' something something '||substr(colB, 4, 8) from table1 where blah=foo

See more details: http://www.sqlite.org/lang_corefunc.html

If I am using a query that spans several tables, like your statement:

select a.col1, b.col3, c.col6 from table1 a, table2 b, table3 c where a.col2=b.col4 and b.col7=c.col2 and a.col4='whatever'

... I prefer to write it like this instead:

select a.col1, b.col3, c.col6 from table1 a join table2 b on a.col2=b.col4 join table3 c on b.col7=c.col2 where a.col4='whatever'

Exactly same results, but it'll be clearer to yourself (and others) which equality clause is meant for joining tables together and which equality clause is acting as a filter.

Sql is a very useful skill to learn, I highly recommend it.

Tom

[Manners]

Thanks Tom.

I did see references to the JOIN function in my sqlite googling but with the app I mentioned I kept bashing away and got the results and was so pleased I haven't yet been back to clean up my syntax to make it any better (plus I do believe in the 'if it ain't broke, don't fix it' when dealing with Tasker.  Using strings looks interesting.  SQL seems so neat and I love the way it debugs back understandable errors.  

I do hope people see my post or any other references to how running sqlite from Tasker is SO easy and extremely powerful for data parsing from data already in your phone.

[Nigel Roy]

How weird, I have been working on a similar thing myself on and off over the past few days.
I found you definitely need to be on a rooted phone, if my understanding is correct the Android system will not allow one application to access another’s data so root is required to read the database.
I agree that the sqlite3 command is an excellent way to get data from the calendar, I used "adb shell" from my laptop to perfect my query but a slightly different method to you, in order to set the time I used a a Tasker variable substitution in the query. I also initially went down the lines of splitting variables but it ended up being quite a long and painful task so I found that I could use "awk" to manipulate the results.

My shell command pipes the sqlite3 result through "awk" to give me a formatted output list of the next 5 reminders which I have just used to populate the field of a Minimalistic Text widget!

Here is the command:
sqlite3 -list /data/data/com.android.providers.calendar/databases/calendar.db "SELECT Instances.begin, Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > %TIMEMS ORDER BY Instances.begin ASC LIMIT 5;" | awk -F "|" '{printf strftime("%a %H:%M",$1/1000)}{print " -",$2}'

Your success with awk may depend on the version of busybox you have installed but you can test this via adb shell which I find is a lot easier than testing it out directly on the phone..
The "awk -F "|"" tells awk that the delimiter is the pipe symbol instead of the default "space".
The "printf strftime" section reformats the time (in awk $1 refers to the first column or field) in seconds to give the 3 letter day followed by 24 hour clock in hours and minutes. Using "printf" means there is no carriage return after the date and time. The second "print" section prints the second column (in my case the event description) after a " -".

The output looks something like this:
Tue 13:00 - Call Someone
Tue 19:00 - Spare key
Wed 08:00 - Someone on holiday
Thu 17:00 - Rubbish
Fri 08:30 - Parking permit

Depending on how you are displaying the information you may find some of it is wrapped but like you I was very pleased with myself for managing to fight my way through the various database tables and SQL options.

I am quite sure there is a lot more potential waiting in there to be unlocked.

One odd thing I found was that although my command worked perfectly from the "adb shell", when I added the "awk" section to the end it stopped working from the Tasker shell command. Not sure why, whether it was a size thing or limitation on the way tasker deals with the shell command but I did get it to work perfectly using "Sceure Settings" command option instead!

[TomL]

On Wednesday, March 20, 2013 5:35:13 AM UTC-4, Nigel Roy wrote:

sqlite3 -list /data/data/com.android.providers.calendar/databases/calendar.db "SELECT Instances.begin, Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > %TIMEMS ORDER BY Instances.begin ASC LIMIT 5;" | awk -F "|" '{printf strftime("%a %H:%M",$1/1000)}{print " -",$2}'

One odd thing I found was that although my command worked perfectly from the "adb shell", when I added the "awk" section to the end it stopped working from the Tasker shell command. Not sure why, whether it was a size thing or limitation on the way tasker deals with the shell command but I did get it to work perfectly using "Sceure Settings" command option instead!

I wonder if maybe Tasker was confused by the %a %H %M  in the awk cmd and tried to treat them as Tasker vars?

I usually do my shell cmd authoring in a shell script:

/data/local/scripts/example.sh

=============

#!/system/bin/sh

sqlite3 something.db "select blah from foo" | awk '/something/ {print $1}';

exit;

=============

... and try it out from a terminal emulator on my phone.  Once the script is perfected, I then call that script in Tasker's RunShell:

Action > Script > RunShell > /data/local/scripts/example.sh

A few advantages to this approach:

1. I don't have to worry about a huge command inside of the RunShell command field.  

2. I can always run the shell script manually from a terminal emulator.

3. Most importantly, I can reuse the functionality of that shell script by calling it from other shell scripts.

It's all a matter of personal taste, so it's all good.

Tom

[Nigel Roy]

After doing a bit more googling and experimentation following Tom's first comments about sqlite3 functions, I discovered that there is a built in "strftime" function within sqlite3 itself. This allowed me to bypass the need for using "awk" to reformat the time.

The resulting query is shown below and produces eaxctly the same output as the "awk" version I used above:

sqlite3 -list -separator ' - ' /data/data/com.android.providers.calendar/databases/calendar.db "SELECT replace(replace(replace(replace(replace(replace(replace(strftime('%w %H:%M', Instances.begin/1000, 'unixepoch'), '0 ', 'Sun '), '1 ', 'Mon '), '2 ', 'Tue '), '3 ', 'Wed '), '4 ', 'Thu '), '5 ', 'Fri '), '6 ', 'Sat '), Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > strftime('%s','now')*1000 ORDER BY Instances.begin ASC LIMIT 5;"

The first "strftime" function converts the time retreied from the database into a weekday number (0-6) followed by the 24hr time of the event. The time used by the Android database is in "milliseconds" whereas the sqlite function expects it in seconds. This is easy to resolve using the "/1000" to convert it to seconds.
The problem I had was that I wanted the weekday to be a 3 letter abbreviated day name and the "strftime" function in sqlite3 does not support that option only the weekday number.
I resolved this problem by using the "replace" function, as you can see this gets a bit messy as there are 7 possible days I had to nest 7 replace statements, one for each day. Also you can't just replace the number as numbers also appear in the time so you could get some very strange looking results. I searched for the weekday number followed by a space, which makes it unique in my result string so works for me.
It's pretty obvious to me that there is huge potential here for both querying the Android databases and also creating your own databases to store any sort of information which could easily be manipulated by Tasker.

I have already created another query to find out when the next alarm is due on my phone, I display the data in a Zoom widget so that it is easy to ensure I will be woken at the correct time and not when I don't need to be! One issue with this is that there is no "standard" alarm clock within Android so you may need to search round and find where alarm data is stored!

Also Tom I think I discovered why I thought my script was not running correctly in the Tasker "run shell" option. After a lot of messing around I found it was actually working, I had just put the output into the wrong variable so it didn't look like it was doing anything. I have tested this last query using "Secure Settings",  as a direct "run shell" command and from inside a script file that tasker calls - all 3 work exactly the same!

Many many hours of fun.

Regards Nigel

[TomL]

On Friday, March 22, 2013 5:24:01 AM UTC-4, Nigel Roy wrote:

The problem I had was that I wanted the weekday to be a 3 letter abbreviated day name and the "strftime" function in sqlite3 does not support that option only the weekday number.

No need for nested replaces.  Here's the way to do it:

http://www.sqlite.org/lang_expr.html#case

select case strftime('%w', Instances.begin/1000, 'unixepoch') when 0 then 'Sun' when 1 then 'Mon' when 2 then 'Tue' when 3 then 'Wed' when 4 then 'Thu' when 5 then 'Fri' else 'Sat' end || ' ' || strftime('%H:%M', Instances.begin/1000, 'unixepoch'), Events.title FROM Instances [etc etc etc]

Tom

[Nigel Roy]

I like that better, although I don't think it makes the query any smaller.

Unfortunately I cannot get it to work or work out why it doesn't!!

I copied your whole section from the select statement to the ",". I get no error messages it just results in all days being Saturday. I removed the "case" sections and confirmed that the strftime function is returning 3 "5" values which should be changed to "Fri" but are not.

Tried exactly the same thing on my Linux PC with exactly the same result so I am not quite sure what is going wrong??

Regards Nigel

[Matt R]

Do you need to include the space after the number?

Matt

[Nigel Roy]

OK solved that little mystery, thanks Matt although it wasn't anything to do with a space you put me onto the right track.
For whatever reason the you need to put single quote marks around the day number, possibly because it is doing string replacement??
So the updated working version looks like this:

sqlite3 -list -separator ' - ' ~/Cal/calendar.db "SELECT CASE strftime('%w', Instances.begin/1000, 'unixepoch') WHEN '0' THEN 'Sun' WHEN '1' THEN 'Mon' WHEN '2' THEN 'Tue' WHEN '3' THEN 'Wed' WHEN '4' THEN 'Thu' WHEN '5' THEN 'Fri' ELSE 'Sat' END || ' ' || strftime('%H:%M', Instances.begin/1000, 'unixepoch'), Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > strftime('%s','now')*1000 ORDER BY Instances.begin ASC LIMIT 5;"

This works on Linux, not tried on my phone yet but see no reason why it shouldn't work there too.

It would be vaguely interesting to know which is the most efficient way to do this, the "replace" or "CASE" options but not really sure how to check that as the results are displayed pretty quickly anyway.

Regards Nigel

[Nigel Roy]

Hi Alexander,

I can't really test to see if it works but looking at your query I think one problem at least is where you are doing the "> date('now')"!

If you test that function in sq3lite on its own you will see the result gives something like this "2013-03-23" whereas the time you are getting from the database should be in ms. Try replacing the "date('now')" with "strftime('%s','now')*1000" so you will be comparing the right sort of values and take it from there.

Regards Nigel

On Saturday, 23 March 2013 02:14:21 UTC, Alexander Budin wrote:

Hi everybody!

I found the discussion here really useful. I'm having an issue and was wondering if someone could point me in the right direction. I'm trying to pull the end time from a calendar appointment and set it aso a tasker variable.

Here's what I got so far: 

sqlite3/data/data/com.android.providers.calendar/databases/calendar.db

SELECT c.end 

FROM Calendars a, Events b, Instances c
WHERE a._id=b.calendar_id
AND b._id=c.Event_id
AND a.name='Main'
AND b.title='Sleep'
AND date(datetime(end / 1000 , 'unixepoch')) > date('now')
GROUP BY a.name,b.title 

I'd like the output to be in 24 hour HH:MM format if possible

Any help would be greatly appreciated.

[Nigel Roy]

OK second attempt after actually looking at what you are trying to do and thinking about it a bit more.

I think you probably want to do change it to this:

SELECT date(datetime(c.end/1000 , 'unixepoch')

FROM Calendars a, Events b, Instances c
WHERE a._id=b.calendar_id
AND b._id=c.Event_id
AND a.name='Main'
AND b.title='Sleep'

AND c.end > strftime('%s','now')*1000
GROUP BY a.name,b.title

Although I am no expert I think you wanmt to do the conversion on the c.end you are selecting not the one you are comparing.

Try it and see where you get.

Regards Nigel

[Alexander Budin]

Hi Nigel,

Thanks for the suggestions. I tried both variants and tested the sql in a debugger on the device and still no luck. I created a test event called "Sleep" in calendar "Main" that is Mar 24 1 pm - 8 pm. Here are the results:

In the debugger, when using Select c.end I get the correct row

The debugger allows selecting the row and showing different views

When using your second query it generates an error:

I guess it doesn't like the date(datetime(c.end/1000, 'unixepoch') context

In tasker here is how my task looks like:

The variable %Calendarresult is blank every time I run the task.

Any chance you could edit the query to return an entry that is in your calendar and see if tasker can store the result in a variable?

Thanks!

Best Regards,

Alexander

On Tuesday, March 19, 2013 6:24:00 AM UTC-4, Manners wrote:

[Nigel Roy]

OK - found another slight error - with the following query I can get a result based a new entry I put in my calendar.

SELECT datetime(c.end/1000 , 'unixepoch')

FROM Calendars a, Events b, Instances c
WHERE a._id=b.calendar_id
AND b._id=c.Event_id
AND a.name='Main'
AND b.title='Sleep'
AND c.end > strftime('%s','now')*1000
GROUP BY a.name,b.title

This gives the result:

2013-03-27 15:00:00

Which matches the new entry I made in the calendar for both date and time!

Is that what you were after?

[Alexander Budin]

Nigel,

The query is perfect, returns time in GMT. However, I am still having a problem with tasker storing the result in a variable.

These are the steps I took to setup the task:

Script > Run Shell

Command: sqlite3 /data/data/com.android.providers.calendar/databases/calendar.db SELECT time(c.end/1000, 'unixepoch') FROM Calendars a, Events b, Instances c WHERE a._id=b.calendar_id AND b._id=c.Event_id AND a.name='Main' AND b.title='Sleep' AND c.end > strftime('%s','now')*1000 GROUP BY a.name, b.title;

Timeout: 0 seconds

Use Root : checked

Store Result in: %ALARM

If: unchecked

Continue Task After Error: unchecked

Label: Run

Were your settings similar?

[TomL]

Alexander, the error message said error near the word FROM. You had na missing parens in your clause "date( datetime( ) ".

Tom

[Nigel Roy]

The variable you want to set should be under the "Store Output In" option, not the "Store Result In".

The result will generally just indicate whether the command has run successfully or not and be 1 or 0 - which is not much use!

The output will contain any text output from the sqlite3 command - which is exactly what you want!

Regards Nigel

On Tuesday, 19 March 2013 10:24:00 UTC, Manners wrote:

[Alexander Budin]

@Tom thanks, we changed it up so it's no longer an issue

@Nigel I must be missing something...

My run shell screen does not have a "Store Output In" option/field

The help file for this screen says the following:

Do we have different version of tasker? I'm running 1.3.3u2m For Android: 1.6+ on Android 4.0.4 I checked in the market and I have an up to date version.

[Nigel Roy]

Yes we do have different versions, I am running the latest beta so that may explain that, there are two things I can suggest to solve this for you:

1) Use the "Secure Settings" plugin to run the sqlite command, that definitely has the option to store the output in a Tasker variable and should do the job for you until Pent puts the finishing touches to the next version.
2) Redirect the output to a file on your SD card then read the result form the file using Tasker.

Regards Nigel

[Alexander Budin]

Nigel,

Using the secure settings plugin did the trick. Thank you so much for all your help!

Best Regards,

Alexander

[Paul [sabret00the]]

This is really interesting, thanks for sharing.

[Paul [sabret00the]]

Just to share, the command that outputted the data I required in the manner I wanted was:

sqlite3 /data/data/com.android.providers.calendar/databases/calendar.db "SELECT strftime('%H:%M', Instances.begin/1000, 'unixepoch'), calendar_displayname, calendar_id, title FROM Instances, Events, Calendars WHERE Instances.event_id = Events._id AND date(datetime(begin / 1000 , 'unixepoch')) = date('now') AND Events.Calendar_id = Calendars._id AND Calendar_id NOT IN(4,6) ORDER BY Instances.begin ASC;"

[agung deppe]

hi, finally found this trick. thanks for sharing..

it works like a charm, but the problem now is how to separate between records,
for example I have 2 records after execute sqlite with secure setting and the outputs is like this:

2013-05-19 | 10:00:00 | Meeting with Customer | at Cafepark
2013-15-19 | 20:00:00 | Buy Train Ticket for holiday | at St.North

there is no " | " between two records. my idea is to say/speak all those events in the morning with TTS voice, something like:
"hi, you have agenda: Meeting with Customer the time is 10:00:00 and the location is at Cafepark"
then continue say with next records..
please help me with the loop/array.

thanks for help.

[Paul [sabret00the]]

The info you're looking for is basically here:
APS.compilespeech (114)
A1: If [ %APSEVENTS Is Set ]
A2: Variable Set [ Name:%newline To:
Do Maths:Off Append:Off ]
A3: Variable Set [ Name:%APSSPEECH To:Morning sir, hope you slept well. Today you have  Do Maths:Off Append:Off ]
A4: Variable Split [ Name:%APSEVENTS Splitter:%newline Delete Base:Off ]
A5: For [ Variable:%apsevent Items:%APSEVENTS() ]
A6: Variable Split [ Name:%apsevent Splitter:| Delete Base:Off ]
A7: Variable Set [ Name:%APSSPEECH To:%apsevent4 at %apsevent1 and then Do Maths:Off Append:On ]
A8: End For
A9: Variable Set [ Name:%APSSPEECH To: You're done. Have a good day.  Do Maths:Off Append:On ]
A10: Else
A11: Variable Set [ Name:%APSSPEECH To:Good morning sir, glad to see you got up okay. Just to let you know, you have nothing scheduled for today.  Do Maths:Off Append:Off ]
A12: End If

--
You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/BecVXt-Yuks/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[TomL]

Where you have this:

/data/data/com.android. providers.calendar/databases/calendar.db

... you should have this:

/data/data/com.android.providers.calendar/databases/calendar.db

In order words, take out the space between "android." and "providers".

Tom

On Wednesday, May 22, 2013 9:53:52 AM UTC-4, Martin Hennessy wrote:

Paul,
I'm running your shell
sqlite3 /data/data/com.android. providers.calendar/databases/calendar.db "SELECT strftime('%H:%M', Instances.begin/1000, 'unixepoch'), calendar_displayname, calendar_id, title FROM Instances, Events, Calendars WHERE Instances.event_id = Events._id AND date(datetime(begin / 1000 , 'unixepoch')) = date('now') AND Events.Calendar_id = Calendars._id AND Calendar_id NOT IN(4,6) ORDER BY Instances.begin ASC;"
through a Secure Settings 'run command' using root and trying to output to a variable.

It wasn't working so I checked 'show output' in the secure settings configuration. It outputs this as an error
sqlite3: Error: too many options: "SELECT strftime... Then the rest of the shell all the way to ASC;"

I'm trying to work out to what 'too many options' refers. Could it be too many calendars to query and it doesn't know which one I want? I have three on my phone.

I'm really new to databases & sql and have been trying to learn by studying other people's shells etc. I've reached the limits of my understanding on this one though!

Ta,
Martin

[Martin Hennessy]

Tom, thanks!

*ALMIGHTY FACEPALM*

I swear a typo will one day destroy the world. In my defence maybe the two words were straddling a line & I couldn't see the space. Well that's my story at least and I'm sticking to it!

[TomL]

When in doubt, change the font to monospaced, make the font size bigger, and twiddle with line wrapping.

:)

Tom

[agung deppe]

hi Paul,
that's awesome! thank you very very much for your help.
just found out there's a variable: %newline
beside, I'm too stupid for programming & logic :-D

and now I have a personal assistant that speaks my agenda in the morning :)
thanks again
--

[Paul [sabret00the]]

Sorry, didn't notice that Tasker didn't output the new line variable very clearly. But I'm glad you have everything working properly. Enjoy :-)

On 25 May 2013 04:51, "agung deppe" <agung...@gmail.com> wrote:

hi Paul,
that's awesome! thank you very very much for your help.
just found out there's a variable: %newline
beside, I'm too stupid for programming & logic :-D

and now I have a personal assistant that speaks my agenda in the morning :)

thanks again.

On Monday, May 20, 2013 1:08:44 PM UTC+7, Paul [sabret00the] wrote:

[gary scarborough]

can you explain the APS .compilespeech (114)

[Paul [sabret00the]]

If %APSEVENTS is set, create a newline variable and set the prefix of
appropriate audio/speech event, use the newline variable to split the
APSEVENTS variable into an array and then set the audio for each event
in that day by looping through the array. After that's done, set the
suffix for days events.

APS.compilespeech (114)
A1: If [ %APSEVENTS Is Set ]
A2: Variable Set [ Name:%newline To:
Do Maths:Off Append:Off ]
A3: Variable Set [ Name:%APSSPEECH To:Morning sir, hope you slept
well. Today you have Do Maths:Off Append:Off ]
A4: Variable Split [ Name:%APSEVENTS Splitter:%newline Delete Base:Off ]
A5: For [ Variable:%apsevent Items:%APSEVENTS() ]
A6: Variable Split [ Name:%apsevent Splitter:| Delete Base:Off ]
A7: Variable Set [ Name:%APSSPEECH To:%apsevent4 at %apsevent1 and
then Do Maths:Off Append:On ]
A8: End For
A9: Variable Set [ Name:%APSSPEECH To: You're done. Have a good day.
Do Maths:Off Append:On ]
A10: Else
A11: Variable Set [ Name:%APSSPEECH To:Good morning sir, glad to see
you got up okay. Just to let you know, you have nothing scheduled for
today. Do Maths:Off Append:Off ]
A12: End If

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Tasker" group.
> To unsubscribe from this topic, visit

> https://groups.google.com/d/topic/tasker/BecVXt-Yuks/unsubscribe.

>
> To unsubscribe from this group and all its topics, send an email to
> tasker+un...@googlegroups.com.

> Visit this group at http://groups.google.com/group/tasker.

>
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

--
Until Next Time, Make Me A Marvel!

[gary scarborough]

paul thanks for quick reply im trying to learn this here as i go thanks

[cruise]

Massive thanks for this!

Parsing the XML from the API was a real pain - can't believe how much easier this is :P

For all those formatting the date and time in the SQL, remember Tasker has a Variable Convert option, which can format a UNIX timestamp into a human readable string. You don't get a choice of formats, admittedly, but it might simplify something for someone :D

Since I use the raw timestamp within my tasks (compare to %TIMES [current time in UNIX timestamp format]) I don't need to do any formatting (apart from dividing by 1000 and I do that when assigning the variable in Tasker).

[gary scarborough]

Cruise how do you apply the variable convert to format the UNIX time stamp?

--

You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/BecVXt-Yuks/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
Visit this group at http://groups.google.com/group/tasker.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Gary Scarborough

[gary scarborough]

Nigel I tried your script through secure settings but I get the error can't open requested database
sqlite3 -list -separator ' - ' ~/Cal/calendar.db "SELECT CASE strftime('%w', Instances.begin/1000, 'unixepoch') WHEN '0' THEN 'Sun' WHEN '1' THEN 'Mon' WHEN '2' THEN 'Tue' WHEN '3' THEN 'Wed' WHEN '4' THEN 'Thu' WHEN '5' THEN 'Fri' ELSE 'Sat' END || ' ' || strftime('%H:%M', Instances.begin/1000, 'unixepoch'), Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > strftime('%s','now')*1000 ORDER BY Instances.begin ASC LIMIT 5;"
Am I inputting incorrectly

[gary scarborough]

This is the error

Error: unable to open database "//Cal/calendar.db": unable to open database file

[TomL]

Your calendar.db file may be in a different spot.  And I think you may need a rooted phone in order to read that file.  Try to locate the calendar.db file on your phone using a folder explorer type app.

On my rooted phone, the file is located at:

/data/data/com.android.providers.calendar/databases/calendar.db

Tom

[gary scarborough]

Yes I am rooted and I checked my calendar is in same location. So I should remove first line that has location and replace with the correct location thanks

[gary scarborough]

Tom thanks that did the job. I have been trying to crash course all of this and its been great and challenging.

--

You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/BecVXt-Yuks/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
Visit this group at http://groups.google.com/group/tasker.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Gary Scarborough

[TomL]

you're welome. being able to explorer the innards of Android is awesome. I can't imagine not being able to pop the hood up and taking a look inside.

[gary scarborough]

so true so true.

On Mon, Jul 1, 2013 at 3:51 PM, TomL <toml...@gmail.com> wrote:

you're welome.  being able to explorer the innards of Android is awesome.  I can't imagine not being able to pop the hood up and taking a look inside.

--
You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/BecVXt-Yuks/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
Visit this group at http://groups.google.com/group/tasker.
For more options, visit https://groups.google.com/groups/opt_out.

--
Gary Scarborough

[gary scarborough]

Is it possible to use a similar script to this one to pull the weather?

[cruise]

Depends on your weather app, but in principle, yes.

Have a look at pocketables.com for the latest Tasker guides - there's a couple there about pulling information out of other apps via the same mechanism.

[Badhri Narayanan]

Hi TomL,

Based on your suggestion I thought of moving my sqlite command into a shell script and then calling it using the Run Shell action in Tasker. After doing this I kept running into some error or other. 

1) So just to make sure that things are working fine i created a file 'test.sh' with the following content.

#!/system/bin/sh

echo 'say hi';

exit;

2) Executed it using adb shell(and then su) and i got the below result.

root@mako:/ # sh /sdcard/Development/Scripts/test.sh

sh /sdcard/Development/Scripts/test.sh

say hi

: not foundelopment/Scripts/test.sh[2]:

root@mako:/ #

What does this ": not foundelopment/Scripts/test.sh[2]:" signify ? And how to get this thing corrected? Thanks in advance.

PS: I verified that sh is present at the mentioned path "#!/system/bin/sh" in my phone

On Wednesday, 20 March 2013 17:16:33 UTC+5:30, TomL wrote:

On Wednesday, March 20, 2013 5:35:13 AM UTC-4, Nigel Roy wrote:

sqlite3 -list /data/data/com.android.providers.calendar/databases/calendar.db "SELECT Instances.begin, Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > %TIMEMS ORDER BY Instances.begin ASC LIMIT 5;" | awk -F "|" '{printf strftime("%a %H:%M",$1/1000)}{print " -",$2}'

One odd thing I found was that although my command worked perfectly from the "adb shell", when I added the "awk" section to the end it stopped working from the Tasker shell command. Not sure why, whether it was a size thing or limitation on the way tasker deals with the shell command but I did get it to work perfectly using "Sceure Settings" command option instead!

I wonder if maybe Tasker was confused by the %a %H %M  in the awk cmd and tried to treat them as Tasker vars?

I usually do my shell cmd authoring in a shell script:

/data/local/scripts/example.sh

=============

#!/system/bin/sh

sqlite3 something.db "select blah from foo" | awk '/something/ {print $1}';

exit;

=============

... and try it out from a terminal emulator on my phone.  Once the script is perfected, I then call that script in Tasker's RunShell:

Action > Script > RunShell > /data/local/scripts/example.sh

A few advantages to this approach:

1. I don't have to worry about a huge command inside of the RunShell command field.  

2. I can always run the shell script manually from a terminal emulator.

3. Most importantly, I can reuse the functionality of that shell script by calling it from other shell scripts.

It's all a matter of personal taste, so it's all good.

Tom

[Nigel Roy]

Hi Badhri,

I think you are probably running into an Android permissions problem. If you search around you will find many posts on the Internet about trying to execute scripts from SD card. Android does not allow this for security reasons.

You should be able to create a directory in your /data partition for your scripts and put them in there, they can then be made executable and run without any problems.

Regards Nigel

[Stathis Efstathiou]

Guys thanks a lot for your posts. They have been very educational. I have read a bit about sqlite and tried some queries with the SQLite Debugger but i am afraid that my knowledge cannot reach that level.

Can you please help with the following query?

I would like to select calendar_name, calendar_title from a specific calendar only and retrieve the events of that specific calendar with start_date tomorrow.

Any help is appreciated.

[Rory Harnisch]

I was told that sqlite3 was supposed to be installed into the xbin folder and not the bin folder, is this true? Did you (op) mean to say xbin or am I wrong? Thanks!

[Paul [sabret00the]]

Basically the command you quoted will work for you, you just need to tell it what the calendar id you want is.

--

You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/BecVXt-Yuks/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
Visit this group at http://groups.google.com/group/tasker.
For more options, visit https://groups.google.com/groups/opt_out.

--

[Bob Hansen]

If you have root you should put it in xbin. If unrooted it would have be in bin and would be unable to access some databases.

[Stathis Efstathiou]

Paul, thanks for the answer. I have been experimenting a bit with SQLite Debugger and definitely sqlite will help me a lot.

OK i have the following query, but i'm not sure how i can get the start_date tomorrow part. 

SELECT Calendars._id, Calendars.calendar_displayName, Events.title, Instances.startDay FROM Calendars, Events, Instances WHERE Calendars._id = 10

Instances.startDay is a number like 2456665...How can i convert it to normal date so i can get startDate=tomorrow?

[Stathis Efstathiou]

OK i'm on a good track now.

SELECT Calendars._id, Calendars.calendar_displayName, Events.title, Instances.startDay FROM Calendars, Events, Instances WHERE Calendars._id = 10 AND  date(startDay) = date('now','+1 day') AND Events._id = Instances.event_id AND Calendars._id = Events.calendar_id

This query gives me the exact result that i am expecting. When running it through Run Shell command though i get an error.

Here is the task description:

Sqlite (27)

A1: Run Shell [ Command:/local/scripts/db Timeout (Seconds):2 Use Root:On Store Output In:%Stathis Store Errors In:%StatErr Store Result In:%Sta Continue Task After Error:On ] 

Here is the error:

Error: incomplete SQL: exit

/local/scripts/db[6]: SELECT Calendars._id, Calendars.calendar_displayName, Events.title, Instances.startDay FROM Calendars, Events, Instances WHERE Calendars._id = 10 AND  date(startDay) = date('now','+1 day') AND Events._id = Instances.event_id AND Calendars._id = Events.calendar_id: not found

And here is the script:

#!/system/bin/sh

#

sqlite3 /data/data/com.android.providers.calendar/databases/calendar.db

"SELECT Calendars._id, Calendars.calendar_displayName, Events.title, Instances.startDay FROM Calendars, Events, Instances WHERE Calendars._id = 10 AND  date(startDay) = date('now','+1 day') AND Events._id = Instances.event_id AND Calendars._id = Events.calendar_id"

Any help is appreciated

[nanobrain]

Make sure there is no carriage return between lines 4 & 5.

[Stathis Efstathiou]

That was it. Perfect. I can continue with my task now. Thanks a lot for your help

[Paul [sabret00the]]

Instead of

date(datetime(begin / 1000 , 'unixepoch')) = date('now')

You want
date(datetime(begin / 1000 , 'unixepoch')) = date('now', '+1')

[Paul [sabret00the]]

Oops, just seen you had it sorted already.

[tulcy]

Hello!

I'm trying to create a profile which would only be active if calendar entry existed that day. I want my phone to change wallpaper if I have calendar events that day. By default, Tasker only offers a profile to be active during events (calendar entry), so I started looking into other options and found your post, which has been helpful, but still I'm having trouble.

The thing is, I can't seem to get access to the calendar database. I tried running the following command via secure settings:

/data/data/com.android.providers.calendar/databases/calendar.db

SELECT begin, calendar_displayname, title FROM Instances, Events, Calendars WHERE Instances.event_id = Events._id AND date(datetime(begin / 1000 , 'unixepoch')) = date('now') AND Events.Calendar_id = Calendars._id ORDER BY Instances.begin ASC;

I get the following errors:
sh:<stdin>[1]: /data/data/com.android.providers.calendar/databases/calendar.db: can't execute: Permission denied
sh:<stdin>[3]: syntax error: '(' unexpected

 
I have root access and have granted it to Tasker and Secure Settings. During execution I get a popup message saying Superuser granted to Secure Settings. Same thing happens if I run the command via Tasker Shell.

Any help would be much appreciated.

[Stathis Efstathiou]

I'm using the Run Shell from tasker but i suppose secure settings can do the job. Make sure to mark Use Root and change your command to:

 

sqlite3 /data/data/com.android.providers.calendar/databases/calendar.db

[tulcy]

Tried that before, got:

sh:<stdin>[1]: sqlite3/data/data/com.android.providers.calendar/databases/calendar.db:not found

I'm using Secure Settings because it shows the output and errors right away. I'm running the code using both Run Shell and SS with same results.

[Stathis Efstathiou]

you need a space between sqlite3 and /data

[in...@thrivepsychology.org.au]

Hi Stathis,

I was wondering if you could help me - I have been trying to use your script (below) that you posted above, however the variable is empty. Tasker is not returning any error codes. I have changed the calendar id to my calendar, and it returns the correct info in sqlite debugger. Any suggestions?

Also, I was wondering if anyone could help me with spitting the output. I want to split using the delimiter |, however I want it to split the variable on every third one - is this possible? Any help would be greatly apprecaited. 

"SELECT Calendars._id, Calendars.calendar_displayName, Events.title, Instances.startDay FROM Calendars, Events, Instances WHERE Calendars._id = 6 AND  date(startDay) = date('now','+1 day') AND Events._id = Instances.event_id AND Calendars._id = Events.calendar_id

[Bob Hansen]

One thing that appears wrong is that your missing the ending quotation mark.

[in...@thrivepsychology.org.au]

great, that helped. However, I am still struggling to split the variable. Can I split based on the delimiter | but not on every one?

[Bob Hansen]

What variable split are you talking about? 

[in...@thrivepsychology.org.au]

Thanks for your help Bob,

Sorry I did not explain myself very well. I want to split the resultant variable into the individual calendar appointments, as the resultant variable includes all the appointment together. I was going to do a variable spit using | (which are created when using sqlite in this way), however I do not want to spit the variable at every instance of |, otherwise it splits each calendar appointment into 5 bits. Therefore, I was wondering if it is possible to do a variable spit based on every 5 occurrence of |? 

Does that make any sense?

[Bob Hansen]

It makes perfect sence. There is no way to split on every 5th occurrence of a character, but the good news is that you can split on a word or phrase, so look for a word or phrase that would make a good split and use that.

Hope that helps.

[Matt R]

You could do a variable search replace action with the regex:
([^|]+?\|){4}[^|]+

Matt

[in...@thrivepsychology.org.au]

Matt thanks for helping. Would you please be able to explain how I would do this in the app? I'm fairly new to tasker

[Bob Hansen]

Matt never fails to amaze me.

Use a Variable Search Replace. Specify the variable to be searched. Copy and paste Matt's regex into the Search parameter (make sure there are no space characters before or after the pasted regex expression). Specify a variable for the Store Matches In option. The results will be in the Store Matches In variable as an array (ie %match1, %match2, etc).

[in...@thrivepsychology.org.au]

Works perfectly! Thanks heaps guys

[jbw]

To continue on with this email thread,  I started playing around with using sqlite3 to pull stuff out of the calendar.db and I am able to pull out the subset of entries that I need but I'm trying to filter the results down to an event two days in the future.  Basically I'm searching through my calendar for upcoming flights two days in advance, if there is one, automatically add a few todo's to my todo list.  Set the alarm, reminders, check-in online, pack, etc.

So far I have the following select statement

select title, strftime("%Y-%m-%d", dtstart/1000, 'unixepoch', 'localtime'), strftime("%H%M", dtstart/1000, 'unixepoch', 'localtime')  from Events where title like 'DEPART%' ORDER by dtstart ASC;

DEPARTURE 6:15 AM Local Time (PHL) - Delta Air Lines, Flight 327 (PHL - ATL)|2014-01-27|0615
DEPARTURE 7:45 PM Local Time (ATL) - US Airways, Flight 2014 (ATL - PHL)|2014-01-30|1945
DEPARTURE 6:15 AM Local Time (PHL) - Delta Air Lines, Flight 327 (PHL - ATL)|2014-02-03|0615
DEPARTURE 6:57 PM Local Time (ATL) - Delta Air Lines, Flight 2094 (ATL - PHL)|2014-02-06|1857
DEPARTURE 6:15 AM Local Time (PHL) - Delta Air Lines, Flight 327 (PHL - ATL)|2014-02-10|0615
DEPARTURE 6:57 PM Local Time (ATL) - Delta Air Lines, Flight 2094 (ATL - PHL)|2014-02-13|1857
DEPARTURE 7:10 PM Local Time (PHL) - US Airways, Flight 2037 (PHL - ATL)|2014-02-23|1910
DEPARTURE 5:30 PM Local Time (ATL) - Delta Air Lines, Flight 2394 (ATL - PHL)|2014-02-27|1730

Now I need to filter it down to events two days in the future I think I append the additional WHERE dtstart = ???  this is where I'm lost. The dates are stored as unix epoch time.  How do I get todays date in unixepoch format and add two days to get events in 2 days time?

 thank,

-jbw

On Thu, Feb 20, 2014 at 9:56 PM, <in...@thrivepsychology.org.au> wrote:

Works perfectly! Thanks heaps guys

--
You received this message because you are subscribed to the Google Groups "Tasker" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tasker+un...@googlegroups.com.

[Matt R]

Tasker variable %TIMES is the current time in unix epoch time. To add two days, add 2*24*60*60 seconds.

Matt

[JBW]

Thanks Matt,

However I was able to figure out how to do it within the sqlite3 query and have the output formatted so I can easily parse it with tasker to grab the data I need.

select title, strftime("%Y-%m-%d", dtstart/1000, 'unixepoch', 'localtime'), strftime("%H%M", dtstart/1000, 'unixepoch', 'localtime')  from Events where title like 'DEPART%'  and strftime("%Y-%m-%d", dtstart/1000,'unixepoch','localtime')=strftime("%Y-%m-%d",'now','+3 days');

DEPARTURE 5:30 PM Local Time (ATL) - Delta Air Lines, Flight 2394 (ATL - PHL)|2014-02-27|1730

-jbw

On Sat, Feb 22, 2014 at 4:13 PM, Matt R <matthew.r...@gmail.com> wrote:

Tasker variable %TIMES is the current time in unix epoch time. To add two days, add 2*24*60*60 seconds.

Matt

[Chop Labalagun]

Thank you, this is what i need :)
i cant wait to test it out!!!!

[Richard Wallace]

Ive just tried this and i get no output...

im sure im doing something wrong but not sure what?

Do i need to replace some of the command with relevance to my calendar?

Any chance of an exported .xml?

 

On Tuesday, 19 March 2013 10:24:00 UTC, Manners wrote:

I saw this article: http://www.pocketables.com/2012/07/tasker-google-calendar-event-announcer-task-download.html about Tasker and calendar events.  I wanted to use Tasker to announce my calendar events but, as my other posts have noted, I'm not a fan of constantly grabbing data from HTTP get and chopping up with variable split, namely because of the added layer of connecting to a web server and all the issues that can crop up with that.  Also my issue is/was I have calendars in my phone that don't come from Google - like my work calendar.  So I started digging around the calendar database on the phone and pleasingly found the data.

After a quick self taught crash course in sqlite3 (which is so pleasant to use) I found a clean, efficient way to grab exactly the data you want from one Tasker action:

- Run Shell

- Command:

              sqlite3/data/data/com.android.providers.calendar/databases/calendar.db

              SELECT begin, calendar_displayname, title FROM Instances, Events, Calendars WHERE Instances.event_id = Events._id AND 

              date(datetime(begin / 1000 , 'unixepoch')) = date('now') AND Events.Calendar_id = Calendars._id ORDER BY Instances.begin ASC;

[Stefano]

Hi there,

I need help with SQL and tasker. I just started to use SQL, since I wanted to work on calendar database. Reading your posts and on internet I tried to do this:

sqlite3 /storage/sdcard1/calendar.db "SELECT Events.title, Events.dtstart, Events.dtend, Events.allDay, Events.duration, Events.calendar_id FROM Events Join Calendars on Events.calendar_id = Calendars._id"

Well, the task starts but just it doesn't stop anymore (I can see the stop button active for ages). I do not get any errors and even if the "kill task in case of error" option is ticked nothing happens. I tried different queries but it looks like when I try to insert the INNER join function everything just stops working. Since sometimes I got "calendar has been stopped" error message during this task, I even copied, in first instance, the calendar.db to SD and then I opened that copy, but nothing is changed. Since this query works find of SQLite debugger, do you think it s a tasker bug? I m using the last beta release (I need the nest IF functions). Could anyone test it on beta and non-beta versions to see if it is something wrong with my ROM (custom 4.4.2 on S2) or the task app? Thank you. Any help is much appreciated! (ps: I installed SQL with SQLite3, I have ROOT and it is checked in the tasker.)

[Jeroen]

Hi Stefano, 

Your query is getting all events from the calendar. That's a bit much and why it's taking so long. Try to limit it with a time constraint or at least a LIMIT 10 parameter at the end of your query.

Op donderdag 3 april 2014 10:25:31 UTC+2 schreef Stefano:

[Jeroen]

He guys. I want to get the title and location of my next calendar event from my phone. I found this shell script , which is almost what I want. However, it gets the first event of the day instead of the next one based on current time. Can someone help me to change the sql to my needs? 

Shell script:
sqlite3 /data/data/com.android.providers.calendar/databases/calendar.db

SELECT begin, title FROM Instances,

Events, Calendars WHERE Instances.event_id = Events._id AND date(datetime(begin / 1000 , 'unixepoch')) = date('now') AND
Events.Calendar_id = Calendars._id 

ORDER BY Instances.begin LIMIT 1;

[Kevin DeVocht]

So I am using Nigel's query and it works but all the dates and times are about 6 hours off.  I am still pretty new to SQL and Tasker and Android so any suggestions would be great

[Kevin DeVocht]

Just solved it.  Put 'localtime' after 'unixepoch' in the query so it now looks like

"SELECT replace(replace(replace(replace(replace(replace(replace(strftime('%w %H:%M', Instances.begin/1000, 'unixepoch', 'localtime'), '0 ', 'Sun '), '1 ', 'Mon '), '2 ', 'Tue '), '3 ', 'Wed '), '4 ', 'Thu '), '5 ', 'Fri '), '6 ', 'Sat '), Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > strftime('%s','now')*1000 ORDER BY Instances.begin ASC LIMIT 5;"

Thanks for everyone who worked out a vast majority of the details on this.  I love the idea of this!

[Rolf Stals]

Try using: SELECT title, dtstart FROM Calendars, Events, Instances WHERE dtstart > strftime('%s','now')*1000 ORDER BY dtstart ACS LIMIT1;

This one is currently working for me but I'll have to see in the long run if it keeps working because if I use LIMIT 5 it returns the same appointment 5 times in a row.

PS. If you want to know the location as well you need to include it in SELECT.

Op donderdag 3 april 2014 19:12:07 UTC+2 schreef Jeroen:

[Jeremy Harris]

I am trying to modify the op's script to list all events, regardless of date. I can get the script below to run just fine in my debugger and also in terminal emulator, but I can't get it running in Run Shell. Does anyone know why?

su
sqlite3 /data/data/com.android.providers.calendar/databases/calendar.db
SELECT Calendars.calendar_displayName, datetime(Instances.begin/1000, 'unixepoch', 'localtime'), Events.title FROM Calendars, Instances, Events WHERE Instances.event_id = Events._id AND Events.calendar_id = Calendars._id ORDER BY Instances.begin ASC;

[Matt R]

You don't need the su command (just check the use root option. And I believe you need the sqlite command in double quotes (starting at SELECT...).

Matt

[Jeremy Harris]

You don't need the su command (just check the use root option. And I believe you need the sqlite command in double quotes (starting at SELECT...).

Thanks, like this?

Test (89)
A1: Run Shell [ Command:sqlite3 /data/data/com.android.providers.calendar/databases/calendar.db
"SELECT Calendars.calendar_displayName, datetime(Instances.begin/1000, 'unixepoch', 'localtime'), Events.title FROM Calendars, Instances, Events WHERE Instances.event_id = Events._id AND Events.calendar_id = Calendars._id ORDER BY Instances.begin ASC"; Timeout (Seconds):0 Use Root:On Store Output In:%SchedOutput Store Errors In:%SchedError Store Result In:%SchedResult ]

I get the error in the pic:

On 25 May 2014 22:10, "Matt R" <matthew.r...@gmail.com> wrote:

You don't need the su command (just check the use root option. And I believe you need the sqlite command in double quotes (starting at SELECT...).

Matt

--
You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/BecVXt-Yuks/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.

Visit this group at http://groups.google.com/group/tasker.

For more options, visit https://groups.google.com/d/optout.

[Jeremy Harris]

I figured it out, I think. I can get Tasker to run the task by filtering the sql a little bit more...I guess I had too many results before?

Does Tasker have any sort of limitation to the number of results?

[Lionel]

Hi,

first of all thanks for this sqlite tips. Sounds it could really help me get what I want, since i'm trying to have an event announcer in the morning for both my gmail & exchange calendars.

I have an HTC phone, so with Sense, the calendar app is different. I haven't been able yet to find where the calendar db is stored. Could it be that it's working differently and that this shell method may only work with google calendar?

Thanks and sorry for the noob question. I'm not familiar with sqlite at all!

[Matt R]

Find out the package name of your calendar app and then look in /data/data/com.yourcalpackagename/databases.

Matt

[Brandon Horwath]

There's a whole project on the user wiki that doesn't require root.

[Matt Angiulo]

This is great! My only issue is that all day events show up the day before at 8pm.  Does anyone have a way to fix this?  I have been googling with no avail. I am also trying to group calendar events under a header.  For example, anything that is happening on friday, will be listed under that and anything on Saturday will be listed under that.  Still working on that.

[Matt R]

Maybe a timezone issue?

Matt

[Matt Angiulo]

in Google calendar itself, it shows on the correct dates.  it is a very weird issue.

[Matt R]

Yes, it could show correctly in google calendar.  Imagine something like google calendar is in +4 timezone (correct for your location) but maybe your device is set to 0 timezone.  Check your device settings, maybe your built-in calendar app settings, google calendar settings, etc.

Matt

[Matt Angiulo]

i didn't think of those, thank you i will look into those settings.

[Matt Angiulo]

i checked all the settings and everything is set to US EST.  It is only with all day events like peoples birthday's in Gmail.  I have other calendars that have all day events as well and they show up 4 hours early.  it is really odd.

[Jeremy Harris]

It is a common problem in sql, displaying the correct date for all day events. I searched for weeks and came up with nothing but a fix on github...I don't know how to get files from there...

But, then I discovered that project that Brandon mentioned a few posts up. It is so much better, that I completely replaced my old sql calendar tasks. Since you can import as a project, you can also tweak it to your liking relatively easily.

[Matt Angiulo]

I will check out the project Brandon recommend. Thanks for your help.

[Jeremy Harris]

I will check out the project Brandon recommend. Thanks for your help.

Do that, for sure.

Also, I forgot that I do actually have a workaround for this problem! It's been a while since I've looked at it, but I am still using sql in 3 profiles that search for calendar events, like "When is Vanessa's birthday?" Or "When was my last sales meeting?" Or "When are my profit projections due?" etc...

I ran into the problem with birthdays because they are all day events. I never did find a solution to fix the dates with sql, but I did successfully manipulate the date with tasker, after the sql is run...

In your sql run shell command, you need to include the column "allDay" from the table "Events" and then set a variable %AllDay to 1 if that column indicates so, after splitting the output. Then I created this separate Date Conversion task that I run with a Perform Task action:

Date Conversion (659)
A1: Variable Split [ Name:%Scheduletext121 Splitter:- Delete Base:Off ]
A2: Variable Set [ Name:%Year To:%Scheduletext1211 Do Maths:Off Append:Off ]
A3: Variable Set [ Name:%Month To:%Scheduletext1212 Do Maths:Off Append:Off ]
A4: Variable Set [ Name:%Date To:%Scheduletext1213 Do Maths:On Append:Off ]
A5: If [ %AllDay ~ 1 ]
A6: Variable Set [ Name:%Date To:%Date + 1 Do Maths:On Append:Off ]
A7: End If
A8: Variable Set [ Name:%FormattedDate To:%Month/%Date/%Year Do Maths:Off Append:Off ]

If you try it, let me know if it works for you or if you have questions! Is relatively easy.

Sorry I forgot about this solution! 

On Jul 26, 2014 8:51 AM, "Matt Angiulo" <ang...@gmail.com> wrote:

I will check out the project Brandon recommend. Thanks for your help.

[Alfarang]

Hi,

i've read this and i tryed to do the comands in abd shell, but couldn´t, I'm probably doing something wrong, so here i put what i've done:

-Open windows cmd,

-adb shell

-su

-"Type comand" like sqlite3 -list /data/data/com.android.providers.calendar/databases/calendar.db "SELECT Instances.begin, Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > %TIMEMS ORDER BY Instances.begin ASC LIMIT 5;" | awk -F "|" '{printf strftime("%a %H:%M",$1/1000)}{print " -",$2}'

-Cmd doesn´t return anything.

I've tryed diferent ways, with my phone and also the emulator, typping first sqlite3.... but no clue how to do it, colud somebody help please.

Thanks in advance.

El miércoles, 20 de marzo de 2013 10:35:13 UTC+1, Nigel Roy escribió:

How weird, I have been working on a similar thing myself on and off over the past few days.
I found you definitely need to be on a rooted phone, if my understanding is correct the Android system will not allow one application to access another’s data so root is required to read the database.
I agree that the sqlite3 command is an excellent way to get data from the calendar, I used "adb shell" from my laptop to perfect my query but a slightly different method to you, in order to set the time I used a a Tasker variable substitution in the query. I also initially went down the lines of splitting variables but it ended up being quite a long and painful task so I found that I could use "awk" to manipulate the results.

My shell command pipes the sqlite3 result through "awk" to give me a formatted output list of the next 5 reminders which I have just used to populate the field of a Minimalistic Text widget!

Here is the command:
sqlite3 -list /data/data/com.android.providers.calendar/databases/calendar.db "SELECT Instances.begin, Events.title FROM Instances JOIN Events ON Instances.event_id = Events._id WHERE begin > %TIMEMS ORDER BY Instances.begin ASC LIMIT 5;" | awk -F "|" '{printf strftime("%a %H:%M",$1/1000)}{print " -",$2}'

Your success with awk may depend on the version of busybox you have installed but you can test this via adb shell which I find is a lot easier than testing it out directly on the phone..
The "awk -F "|"" tells awk that the delimiter is the pipe symbol instead of the default "space".
The "printf strftime" section reformats the time (in awk $1 refers to the first column or field) in seconds to give the 3 letter day followed by 24 hour clock in hours and minutes. Using "printf" means there is no carriage return after the date and time. The second "print" section prints the second column (in my case the event description) after a " -".

The output looks something like this:
Tue 13:00 - Call Someone
Tue 19:00 - Spare key
Wed 08:00 - Someone on holiday
Thu 17:00 - Rubbish
Fri 08:30 - Parking permit

Depending on how you are displaying the information you may find some of it is wrapped but like you I was very pleased with myself for managing to fight my way through the various database tables and SQL options.

I am quite sure there is a lot more potential waiting in there to be unlocked.

One odd thing I found was that although my command worked perfectly from the "adb shell", when I added the "awk" section to the end it stopped working from the Tasker shell command. Not sure why, whether it was a size thing or limitation on the way tasker deals with the shell command but I did get it to work perfectly using "Sceure Settings" command option instead!