android - table - sqlite timestamp




Melhor maneira de trabalhar com datas no Android SQLite (6)

Estou com problemas para trabalhar com datas no meu aplicativo Android que usa o SQLite. Eu tenho algumas perguntas:

  1. Que tipo devo usar para armazenar datas no SQLite (texto, inteiro, ...)?
  2. Dada a melhor maneira de armazenar datas, como posso armazená-lo corretamente usando ContentValues?
  3. Qual é a melhor maneira de recuperar a data do banco de dados SQLite?
  4. Como fazer um sql selecionar no SQLite, ordenando os resultados por data?

  1. Como presumido neste comentário , eu sempre usaria números inteiros para armazenar datas.
  2. Para armazenar, você poderia usar um método utilitário

    public static Long persistDate(Date date) {
        if (date != null) {
            return date.getTime();
        }
        return null;
    }

    igual a:

    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME, persistDate(entity.getDate()));
    long id = db.insertOrThrow(TABLE_NAME, null, values);
  3. Outro método utilitário cuida do carregamento

    public static Date loadDate(Cursor cursor, int index) {
        if (cursor.isNull(index)) {
            return null;
        }
        return new Date(cursor.getLong(index));
    }

    pode ser usado assim:

    entity.setDate(loadDate(cursor, INDEX));
  4. Ordenar por data é simples cláusula SQL ORDER (porque temos uma coluna numérica). O seguinte irá ordenar decrescente (a data mais nova é a primeira):

    public static final String QUERY = "SELECT table._id, table.dateCol FROM table ORDER BY table.dateCol DESC";
    
    //...
    
        Cursor cursor = rawQuery(QUERY, null);
        cursor.moveToFirst();
    
        while (!cursor.isAfterLast()) {
            // Process results
        }

Sempre certifique-se de armazenar o horário UTC / GMT , especialmente ao trabalhar com java.util.Calendar e java.text.SimpleDateFormat que usam o fuso horário padrão (ou seja, o dispositivo). java.util.Date.Date() é seguro para usar, pois cria um valor UTC.



A melhor maneira de armazenar a date no SQlite DB é armazenar os atuais DateTimeMilliseconds . Abaixo está o trecho de código para fazer so_

  1. Obter o DateTimeMilliseconds
public static long getTimeMillis(String dateString, String dateFormat) throws ParseException {
    /*Use date format as according to your need! Ex. - yyyy/MM/dd HH:mm:ss */
    String myDate = dateString;//"2017/12/20 18:10:45";
    SimpleDateFormat sdf = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);
    Date date = sdf.parse(myDate);
    long millis = date.getTime();

    return millis;
}
  1. Inserir os dados no seu banco de dados
public void insert(Context mContext, long dateTimeMillis, String msg) {
    //Your DB Helper
    MyDatabaseHelper dbHelper = new MyDatabaseHelper(mContext);
    database = dbHelper.getWritableDatabase();

    ContentValues contentValue = new ContentValues();
    contentValue.put(MyDatabaseHelper.DATE_MILLIS, dateTimeMillis);
    contentValue.put(MyDatabaseHelper.MESSAGE, msg);

    //insert data in DB
    database.insert("your_table_name", null, contentValue);

   //Close the DB connection.
   dbHelper.close(); 

}

Now, your data (date is in currentTimeMilliseconds) is get inserted in DB .

O próximo passo é, quando você deseja recuperar dados do banco de dados, é necessário converter os respectivos milissegundos de data e hora em data correspondente. Abaixo está o snippet de código de amostra para fazer o mesmo_

  1. Converter milissegundos de data em string de data.
public static String getDate(long milliSeconds, String dateFormat)
{
    // Create a DateFormatter object for displaying date in specified format.
    SimpleDateFormat formatter = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);

    // Create a calendar object that will convert the date and time value in milliseconds to date.
    Calendar calendar = Calendar.getInstance();
    calendar.setTimeInMillis(milliSeconds);
    return formatter.format(calendar.getTime());
}
  1. Agora, finalmente, buscar os dados e ver o seu trabalho ...
public ArrayList<String> fetchData() {

    ArrayList<String> listOfAllDates = new ArrayList<String>();
    String cDate = null;

    MyDatabaseHelper dbHelper = new MyDatabaseHelper("your_app_context");
    database = dbHelper.getWritableDatabase();

    String[] columns = new String[] {MyDatabaseHelper.DATE_MILLIS, MyDatabaseHelper.MESSAGE};
    Cursor cursor = database.query("your_table_name", columns, null, null, null, null, null);

    if (cursor != null) {

        if (cursor.moveToFirst()){
            do{
                //iterate the cursor to get data.
                cDate = getDate(cursor.getLong(cursor.getColumnIndex(MyDatabaseHelper.DATE_MILLIS)), "yyyy/MM/dd HH:mm:ss");

                listOfAllDates.add(cDate);

            }while(cursor.moveToNext());
        }
        cursor.close();

    //Close the DB connection.
    dbHelper.close(); 

    return listOfAllDates;

}

Espero que isso ajude a todos! :)


Eu prefiro isso. Este não é o melhor caminho, mas uma solução rápida.

//Building the table includes:
StringBuilder query= new StringBuilder();
query.append("CREATE TABLE "+TABLE_NAME+ " (");
query.append(COLUMN_ID+"int primary key autoincrement,");
query.append(COLUMN_CREATION_DATE+" DATE)");

//Inserting the data includes this:
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
values.put(COLUMN_CREATION_DATE,dateFormat.format(reactionGame.getCreationDate())); 

// Fetching the data includes this:
try {
   java.util.Date creationDate = dateFormat.parse(cursor.getString(0);
   YourObject.setCreationDate(creationDate));
} catch (Exception e) {
   YourObject.setCreationDate(null);
}

O SQLite pode usar tipos de dados de texto, real ou inteiro para armazenar datas. Ainda mais, sempre que você realizar uma consulta, os resultados serão exibidos usando o formato %Y-%m-%d %H:%M:%S

Agora, se você inserir / atualizar valores de data / hora usando funções de data / hora do SQLite, também poderá armazenar milissegundos. Se esse for o caso, os resultados serão exibidos usando o formato %Y-%m-%d %H:%M:%f . Por exemplo:

sqlite> create table test_table(col1 text, col2 real, col3 integer);
sqlite> insert into test_table values (
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123')
        );
sqlite> insert into test_table values (
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126')
        );
sqlite> select * from test_table;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

Agora, fazendo algumas consultas para verificar se somos realmente capazes de comparar tempos:

sqlite> select * from test_table /* using col1 */
           where col1 between 
               strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.121') and
               strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123

Você pode verificar o mesmo SELECT usando col2 e col3 e obterá os mesmos resultados. Como você pode ver, a segunda linha (126 milissegundos) não é retornada.

Note que BETWEEN é inclusivo, portanto ...

sqlite> select * from test_table 
            where col1 between 
                 /* Note that we are using 123 milliseconds down _here_ */
                strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123') and
                strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');

... retornará o mesmo conjunto.

Tente brincar com intervalos de data / hora diferentes e tudo vai se comportar como esperado.

Que tal sem função strftime ?

sqlite> select * from test_table /* using col1 */
           where col1 between 
               '2014-03-01 13:01:01.121' and
               '2014-03-01 13:01:01.125';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123

Que tal sem função strftime e sem milissegundos?

sqlite> select * from test_table /* using col1 */
           where col1 between 
               '2014-03-01 13:01:01' and
               '2014-03-01 13:01:02';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

E quanto a ORDER BY ?

sqlite> select * from test_table order by 1 desc;
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
sqlite> select * from test_table order by 1 asc;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

Funciona muito bem.

Finalmente, ao lidar com operações reais dentro de um programa (sem usar o executável sqlite ...)

BTW: Estou usando o JDBC (não tenho certeza sobre outras linguagens) ... o driver sqlite-jdbc v3.7.2 do xerial - talvez revisões mais novas mudem o comportamento explicado abaixo ... Se você está desenvolvendo no Android, você não precisa de um driver jdbc. Todas as operações SQL podem ser enviadas usando o SQLiteOpenHelper .

O JDBC possui diferentes métodos para obter valores reais de data / hora de um banco de dados: java.sql.Date , java.sql.Time e java.sql.Timestamp .

Os métodos relacionados em java.sql.ResultSet são (obviamente) getDate(..) , getTime(..) e getTimestamp() respectivamente.

Por exemplo:

Statement stmt = ... // Get statement from connection
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_TABLE");
while (rs.next()) {
    System.out.println("COL1 : "+rs.getDate("COL1"));
    System.out.println("COL1 : "+rs.getTime("COL1"));
    System.out.println("COL1 : "+rs.getTimestamp("COL1"));
    System.out.println("COL2 : "+rs.getDate("COL2"));
    System.out.println("COL2 : "+rs.getTime("COL2"));
    System.out.println("COL2 : "+rs.getTimestamp("COL2"));
    System.out.println("COL3 : "+rs.getDate("COL3"));
    System.out.println("COL3 : "+rs.getTime("COL3"));
    System.out.println("COL3 : "+rs.getTimestamp("COL3"));
}
// close rs and stmt.

Como o SQLite não possui um tipo de dados DATE / TIME / TIMESTAMP real, todos esses 3 métodos retornam valores como se os objetos tivessem sido inicializados com 0:

new java.sql.Date(0)
new java.sql.Time(0)
new java.sql.Timestamp(0)

Então, a questão é: como podemos realmente selecionar, inserir ou atualizar objetos de Data / Hora / Registro de Data e Hora? Não há uma resposta fácil. Você pode tentar combinações diferentes, mas eles irão forçá-lo a incorporar funções SQLite em todas as instruções SQL. É muito mais fácil definir uma classe de utilitário para transformar texto em objetos Date dentro de seu programa Java. Mas lembre-se sempre que o SQLite transforma qualquer valor de data em UTC + 0000.

Em resumo, apesar da regra geral de usar sempre o tipo de dados correto, ou mesmo inteiros denotando o tempo Unix (milissegundos desde a época), acho muito mais fácil usar o formato SQLite padrão ( '%Y-%m-%d %H:%M:%f' ou em Java 'yyyy-MM-dd HH:mm:ss.SSS' ) em vez de complicar todas as suas instruções SQL com funções SQLite. A primeira abordagem é muito mais fácil de manter.

TODO: Vou verificar os resultados ao usar getDate / getTime / getTimestamp dentro do Android (API15 ou melhor) ... talvez o driver interno seja diferente do sqlite-jdbc ...


Você pode usar um campo de texto para armazenar datas no SQLite .

Armazenar datas no formato UTC, o padrão se você usar datetime('now') (yyyy-MM-dd HH:mm:ss) permitirá classificação na coluna de data.

Recuperando datas como strings do SQLite você pode formatá-las / convertê-las conforme necessário em formatos regionalizados locais usando o método Calendar ou o método android.text.format.DateUtils.formatDateTime .

Aqui está um método de formatador regionalizado que uso;

public static String formatDateTime(Context context, String timeToFormat) {

    String finalDateTime = "";          

    SimpleDateFormat iso8601Format = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");

    Date date = null;
    if (timeToFormat != null) {
        try {
            date = iso8601Format.parse(timeToFormat);
        } catch (ParseException e) {
            date = null;
        }

        if (date != null) {
            long when = date.getTime();
            int flags = 0;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
            flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;

            finalDateTime = android.text.format.DateUtils.formatDateTime(context,
            when + TimeZone.getDefault().getOffset(when), flags);               
        }
    }
    return finalDateTime;
}




date