SQLite3 help

[Friend1]

Hi Desmanto,

I have a problem on SQL statment for SQLite3 when query prayertimes on database. SQL is working, but problem on variables. The SQL is:
query_get='SELECT dayofyear,day,month,prayertimes FROM namazvakti WHERE month=8 and day=11;';
I can't retrieve month (8) and day (11) by using {triggertime, dateformat, MM} and {triggertime, dateformat, dd} in script. How to get month and day values from date? After solving the problem i will share my flow in automagic forum. Thanks in advance.

[Desmanto]

You want to put 8 from month and 11 from day right? Just put it to different variable first, so easier to see. Remember to use double quote when you use inline expression in braces.

Code: Select all

month = "{triggertime,dateformat,M}";
day = "{triggertime,dateformat,d}";
query_get = "SELECT dayofyear,day,month,prayertimes FROM namazvakti WHERE month={month}" and day={day}";

[Friend1]

Hello DESMANTO,

Thank you for quick reply. Now i add language option to flow and will share it on automagic forum.

[Friend1]

Hello Desmanto,

I need to calculate differency of two times for next praytime. For example "14:15" and "17:05" but for each pray time. Can you give me a scrpt to do this? In this example current time is "14:15" and praytime is "17:05". Thanks.

[Desmanto]

To get date from string, use the getDate(), using the pattern character to catch the proper time. hour is HH (hh is for am/pm version), minute is mm.

Code: Select all

c = "14:15";
p = "17:05";

ctime = getDate(c, "HH:mm");
ptime = getDate(p, "HH:mm");

if(ptime < ctime)
  ptime = ptime + 86400000;

dif = ptime - ctime;

c is current time, p is praytime. we need to convert them to time first, as ctime and ptime. Remember, the converted time is started from 1st January 1970 (unix epoch time), deducted by your timezone. In this code, it is working properly. But if you use current time by the triggertime, you have to converted it first to string HH:mm and getDate() back (this is much faster than long calculation).

the if() is there to ensure no negative time. When current time is 22:00 and the praytime is 06:00, you will get negative and wrong value. So we plus it 24 hours first before subtracting.
dif is the difference of ctime and ptime in miliseconds. You can convert it to string, by using getDurationString() or dateformat if you need custom format.

[Friend1]

Hello Desmanto,

Thank you for helps. Can i use variable for timer interval to prevent quick battery drain? If it's possible, i will set global_time to 15m and flow executed 4 times per hour, when praytime < 15 i will set global_time to 1m. If not, any other idea?
Regards,
Friend1

[Desmanto]

Uhm, why use periodic timer trigger? Do you really need to check it periodically? The praytime is time event, you should use time event trigger, which is Global Variable Datetime.

Just continue the same script from above, and at the end add the dif to the current time. You probably want notification 5 minutes before, so subtract it by 300000.

Code: Select all

c = "14:15";
p = "17:05";

ctime = getDate(c, "HH:mm");
ptime = getDate(p, "HH:mm");

if(ptime < ctime)
  ptime = ptime + 86400000;

dif = ptime - ctime;

global_praytime = getDate() + dif - 300000;

use global_praytime as the variable in the trigger Global Variable DateTime. Next time it triggerred based on the praytime, do the calculation again to check next praytime, so the global_praytime will be always filled with the next praytime schedule. Your flow will be only running at the next schedule, very efficient and battery friendly.

More about why we should use glovar datetime : viewtopic.php?f=5&t=7251

[Friend1]

Hello Desmanto,

I told you to share at the end of the prayer time flow, but since ROOT is required for working with the database, I also created a flow to run WITHOUT ROOT (using CSV formatted file). What I have not been able to do is to take the phone automatically during the prayer time and automatically take it out silently until the prayer ends. I am waiting for your help in this regard. Thank you so much.
Link for Rooted PrayerTimes http://automagic4android.com/flow.php?i ... 9386874e67
Link for No Root PrayerTimes http://automagic4android.com/flow.php?i ... a21521a671
To get data files for the flows you can use http://www.namazvakti.com (suppprted many languages) site to download one year prayertimes in XML format. Next step is XML to SQLite3 or CSV conversion. After this put data file in Automgic/db/ (Automagic for csv file) directory and enjoy. In this example i used USA London praytimes. Using direct XML is a good idea, but I can not, excuse me...