SQLite e o trabalho com campos do tipo data, o que fazer?

Bem, voltando, lembrando que este espaço não tem o proposito de ser a melhor fonte de pesquisa ou mesmo referencia sobre os assuntos abordados aqui, mas sim, para documentar as experiencias inusitadas que passamos em alguns projetos.
Há alguns meses fomos contratados para desenvolver um aplicativo para um curso online para auxiliar concurseiros a organizar seus estudos, o aplicativo não é a atividade final, mas sim uma das muitas ferramentas que o idealizador dispõem para seus muitos clientes.
A princípio o aplicativo é uma coletânea de ferramentas de vários outros unificadas em um único lugar. Até ai, nada de mais, a não ser pelo fato de ter que usar o SQLite para armazenar os dados e ai começaram alguns entraves, com coisas simples.
SQLite é uma biblioteca de banco de dados compacta, foi projetado para ser distribuído embutido com a aplicação, porém muito poderoso, contem tudo que os bancos relacionais tem e com a grande vantagem de ser empacotado com a aplicação e oferecer mais flexibilidade no manuseio de dados.
O SQLite não contem muitos tipos de dados primitivos e a principio causa estranheza, e um dos tipos que não aparecem por lá são os tipos para armazenar datas e horas... e agora?...
Bem pra ser simples, vamos dizer que existem 3 estrategias para armazenar datas, são elas:

1. Armazenar como um texto e ja aproveite para deixar no formato yyyy-MM-dd hh:mm //data e hora...
2. Armazenar como um inteiro no formato Unix time
3. Armazenar como REAL no formato Juliano.

No meu caso, escolhi a primeira estratégia, utilizei um método auxiliar em java para recuperar a data no formato desejado, conforme abaixo:

public static String getDate(){
    Calendar c = Calendar.getInstance();    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm");    return df.format(c.getTime());}

OK, tudo bem, ficou a data bonitinha no banco conforme o esperado, mas.... e na hora de construir os relatórios?

Ai surgiram os problemas, afinal trata-se de um campo texto, como fazer as instruções para recuperar uma vez que não tenho datas e preciso emitir relatórios por dia, semana, mês e semestre,,,,

A melhor solução encontrada foi fazer conversão de tipo em tempo de execução do campo que armazena a data para um formato date reconhecido pelo SQLite.


Para o relatorio diário não foi dificil perceber que tratava-se apenas de um select simples sem conversão de tipos, como no exemplo abaixo:

public List getRelatorio(){
    Cursor c = this.banco.rawQuery("select * from atividade where data like ?", new String[]{Utils.getDateAmericana()});

    return getLista(c);}

Abaixo seque o método getDateAmericana:

public static String getDateAmericana(){
    Calendar c = Calendar.getInstance();    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");    return df.format(c.getTime());
}

Para os relatórios semanal, mensal e semestral a solução esta logo abaixo com uma pequena modificação, onde se lê, '-7 days' escreve-se '-30 days' para imprimir os ultimos 30 dias e '-180 days' para imprimir os últimos seis meses.:

public List getRelatorioSemana(){

    Cursor c = this.banco.rawQuery("select  sum((strftime('%s',at.horafinal) - strftime('%s',at.horainicio))/60) as duracao   

from atividade at where date(at.data) between date('now','-7 days') and date('now')  group by tr.tipo, mt.nome, mt.cor", null);
   return getLista(c);
}