Menghitung Waktu Sholat

rencananya sih, *awalnya* mo usulin fitur jadwal sholat online utk di-embedd di aplikasi
*aslinya* sih mo cari RSS Feed google gadget yg udah nyedia-in itu jadi pengennya sih tinggal ambil doank...
*eh ketemunya* dokumen ttg algoritma waktu sholat (donlot di sini)
*penasaran mode = on*
*iseng mode = on*
*suntuk mode = on* - habis debat ama "mamanya anak2" (hiks...)

rancang2 tabel dulu :
+----+------+----------+-----------+----------+------------+
| ID | kota | Latitude | Longitude | timezone | ketinggian |
+----+------+----------+-----------+----------+------------+

DDL nya :

CREATE TABLE `tbl_city_coordinate` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`kota` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`timezone` float NOT NULL,
`ketinggian` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

*googling tentang koordinat astronomis kota jakarta*
*isi-isi data dulu*

INSERT INTO `tbl_city_coordinate` VALUES ('1', 'JAKARTA', '-6.21522', '106.818', '7', '100');


trus bikin stored function nya, ngikutin pedoman algoritma :

CREATE DEFINER=`root`@`localhost` FUNCTION `sf_waktusholat`(vKodeCity int, vTanggal date, idxwaktusholat tinyint) RETURNS varchar(20) CHARSET utf8
BEGIN
declare B float;
declare L float;
declare R float;
declare H float;
declare J int;
declare beta float;
declare D float;
declare T float;
declare Gd float;
declare Gn float;
declare Z float;
declare U float;
declare Vd float;
declare Vn float;
declare W float;
declare hasil float;
declare jam varchar(20);

set j = dayofyear(vTanggal);
set Gd = 19;
set Gn = 19;

SELECT 2 * pi() * J / dayofyear(CONCAT(year(now()),'-12-31')) INTO beta;
SELECT (180/pi()) * (0.006918 - (0.399912 * cos(beta))+(0.070257 * sin(beta))-(0.006758 * cos(2*beta))+(0.000907*sin(2*beta))-(0.002697*cos(3*beta))+(0.001480*sin(3*beta))) INTO D;

SELECT 229.18*(0.000075+(0.001868*cos(beta))-(0.032077*sin(beta))-(0.014615*cos(2*beta))-(0.040849*sin(2*beta))) INTO T;

SELECT latitude, longitude, (timezone) * 15, ketinggian
INTO B,L,R,H
FROM tbl_city_coordinate
WHERE id = vKodeCity;

SET Z = 12 + ((R-L)/15) - (T/60);
SET U = (180/(15*pi())) * ACOS((SIN((-0.8333-(0.0347 * SIGN(H) * SQRT(ABS(H))))*pi()/180)- SIN(D * pi() / 180) * SIN(B * pi() / 180) )/(COS(D * pi()/180) * COS(B * pi()/180)));
SET Vd = (180/(15*pi())) * ACOS((-1 * SIN(Gd * pi()/180) - SIN(D * pi()/180) * SIN(B * pi()/180))/(COS(D * pi()/180) * COS(B * pi()/180)));
SET Vn = (180/(15*pi())) * ACOS((-1 * SIN(Gn * pi()/180) - SIN(D * pi()/180) * SIN(B * pi()/180))/(COS(D * pi()/180) * COS(B * pi()/180)));
SET W = (180/(15*pi())) * ACOS((SIN(ATAN(1/(1 + TAN(ABS(B-D) * pi()/180)))) - SIN(D * pi()/180) * SIN(B * pi()/180))/(COS(D * pi()/180) * COS(B * pi()/180)));

case idxwaktusholat
when 0 then SET hasil = (z - Vd) - 0.134; /*imsyak*/
when 1 then SET hasil = Z - Vd - 0.033 ; /*subuh*/
when 2 then SET hasil = Z - U; /*matahari terbit*/
when 3 then SET hasil = Z + 0.05; /*zuhur*/
when 4 then SET hasil = Z + W + 0.033; /*ashar*/
when 5 then SET hasil = Z + U + 0.033; /*maghrib*/
when 6 then SET hasil = Z + Vn - 0.0167; /*isya*/
end case;

SET Jam = SEC_TO_TIME(hasil*3600);
RETURN jam;
END;


coba bikin stored procedure utk nampilin waktu sholat :

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_waktusholat_tampil`(vIDKota int, tanggal date)
BEGIN
IF vIDKota <= 0 THEN
SELECT Kota, sf_waktusholat(id, date(tanggal), 1) as `Subuh`,
sf_waktusholat(id, date(tanggal), 3) as `Dhuhur`,
sf_waktusholat(id, date(tanggal), 4) as `Ashar`,
sf_waktusholat(id, date(tanggal), 5) as `Maghrib`,
sf_waktusholat(id, date(tanggal), 6) as `Isya`
FROM tbl_city_coordinate
ORDER BY Kota ASC;
ELSE
SELECT Kota, sf_waktusholat(id, date(tanggal), 1) as `Subuh`,
sf_waktusholat(id, date(tanggal), 3) as `Dhuhur`,
sf_waktusholat(id, date(tanggal), 4) as `Ashar`,
sf_waktusholat(id, date(tanggal), 5) as `Maghrib`,
sf_waktusholat(id, date(tanggal), 6) as `Isya`
FROM tbl_city_coordinate
WHERE id = vIDKota;
END IF;
END;

*coba dieksekusi tu SP :
call sp_waktusholat_tampil(1,'2008-04-01');

*hasilnya :
+---------+----------+----------+----------+----------+----------+
| Kota | Subuh | Dhuhur | Ashar | Maghrib | Isya |
+---------+----------+----------+----------+----------+----------+
| JAKARTA | 04:40:05 | 11:59:33 | 15:14:20 | 18:01:09 | 19:10:03 |
+---------+----------+----------+----------+----------+----------+

... cukup memuaskan ...

*kurang lebih lah dengan jadwal sholat utk wilayah Jakarta yg sekarang nampang di republika-online

script lengkap bisa didonlot di sini...

TIDUR AAAH...