WhatsApp_search_for_number_calls.sql

SQLite script fror search calls for BD WhastApp.sql

SELECT * FROM(
		SELECT SUBSTR(mes.key_remote_jid, 1,12) AS [Контакт], DATETIME(SUBSTR(cal.timestamp , 1,10), 'unixepoch', 'localtime')  AS [Дата],
			TIME(cal.duration,'unixepoch') AS [Продолжительность], CASE mes.key_from_me
			WHEN "1" THEN 'Набранный'
			WHEN "0" THEN 'Принятый'
			END AS [Тип]
		FROM call_logs cal 
		JOIN messages mes ON mes._id = cal.message_row_id
	Union all
		SELECT SUBSTR(mes.key_remote_jid, 1,12) AS [Контакт], DATETIME(SUBSTR(mcall.timestamp , 1,10), 'unixepoch', 'localtime')  AS [Дата],
			"00:00:00" as [Продолжительность],"Пропущенный" AS [Тип]   
			FROM  missed_call_logs mcall
			JOIN messages mes ON mes._id = mcall.message_row_id
) WHERE  Контакт = '380*********'
	ORDER BY Дата ASC