Meilleure façon de travailler avec des dates dans Android SQLite

Question

J'ai quelques difficultés à travailler avec des dates dans mon application Android qui utilise SQLite. J'ai quelques questions :

1. Quel type dois-je utiliser pour stocker des dates dans SQLite (texte, entier, ...) ?
2. Étant donné la meilleure façon de stocker les dates, comment puis-je les stocker correctement en utilisant les ContentValues ?
3. Quelle est la meilleure façon d'extraire la date de la base de données SQLite ?
4. Comment faire un sql select sur SQLite, en ordonnant les résultats par date ?

Answer 1

Le meilleur moyen est de stocker les dates sous la forme d'un nombre, obtenu à l'aide de la commande Calendrier.

//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_DATETIME+" int)");

//And inserting the data includes this:
values.put(COLUMN_DATETIME, System.currentTimeMillis()); 

Pourquoi faire ça ? Tout d'abord, il est facile d'obtenir des valeurs à partir d'une plage de dates. Il suffit de convertir votre date en millisecondes, puis d'effectuer la requête correspondante. Le tri par date est tout aussi simple. Les appels pour convertir entre différents formats sont également faciles, comme je l'ai inclus. En résumé, avec cette méthode, vous pouvez faire tout ce dont vous avez besoin, sans problème. Il sera légèrement difficile de lire une valeur brute, mais elle compense largement ce léger inconvénient en étant facilement lisible et utilisable par la machine. En fait, il est relativement facile de construire un lecteur (et je sais qu'il en existe) qui convertira automatiquement l'heure en date pour faciliter la lecture.

Il est utile de mentionner que les valeurs qui en sortent doivent être longues, et non int. Entier dans sqlite peut signifier beaucoup de choses, de 1 à 8 octets, mais pour presque toutes les dates, 64 bits, ou un long, est ce qui fonctionne.

EDIT : Comme cela a été souligné dans les commentaires, vous devez utiliser la fonction cursor.getLong() pour obtenir correctement l'horodatage si vous faites cela.

Answer 2

Vous pouvez utiliser un champ de texte pour stocker des dates dans le cadre de l'opération SQLite .

Stockage des dates au format UTC, par défaut si vous utilisez l'option datetime('now') (yyyy-MM-dd HH:mm:ss) permettra alors de trier par la colonne date.

Récupération des dates sous forme de chaînes de caractères à partir de SQLite vous pouvez ensuite les formater/convertir selon vos besoins dans des formats régionaux locaux en utilisant le calendrier ou l'outil de gestion de l'information. android.text.format.DateUtils.formatDateTime méthode.

Voici une méthode de formatage régionalisée que j'utilise ;

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;
}

Answer 3

1. Comme présumé dans ce commentaire j'utiliserais toujours des entiers pour stocker les dates.

2. Pour le stockage, vous pouvez utiliser une méthode utilitaire

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

   comme ça :

   ContentValues values = new ContentValues();
   values.put(COLUMN_NAME, persistDate(entity.getDate()));
   long id = db.insertOrThrow(TABLE_NAME, null, values);

3. Une autre méthode utilitaire s'occupe du chargement

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

   peut être utilisé comme suit :

   entity.setDate(loadDate(cursor, INDEX));

4. Le classement par date est un simple SQL clause ORDER (parce que nous avons une colonne numérique). L'ordre suivant sera décroissant (c'est-à-dire que la date la plus récente sera la première) :

   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
       }

Veillez à toujours conserver les Heure UTC/GMT surtout lorsqu'on travaille avec java.util.Calendar et java.text.SimpleDateFormat qui utilisent le fuseau horaire par défaut (c'est-à-dire celui de votre appareil). java.util.Date.Date() est sûr à utiliser car il crée une valeur UTC.

Answer 4

SQLite peut utiliser des types de données texte, réels ou entiers pour stocker les dates. De plus, lorsque vous effectuez une requête, les résultats sont affichés au format %Y-%m-%d %H:%M:%S .

Maintenant, si vous insérez/mettez à jour des valeurs de date/heure en utilisant les fonctions de date/heure de SQLite, vous pouvez également stocker des millisecondes. Si c'est le cas, les résultats sont affichés en utilisant le format %Y-%m-%d %H:%M:%f . Par exemple :

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

Maintenant, je fais quelques requêtes pour vérifier si nous sommes réellement capables de comparer les temps :

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

Vous pouvez vérifier le même SELECT en utilisant col2 et col3 et vous obtiendrez les mêmes résultats. Comme vous pouvez le voir, la deuxième ligne (126 millisecondes) n'est pas renvoyée.

Notez que BETWEEN est inclusif, donc...

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');

... retournera le même ensemble.

Essayez de jouer avec différentes plages de dates/heures et tout se passera comme prévu.

Et sans strftime fonction ?

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

Et sans strftime et pas de millisecondes ?

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

Qu'en est-il 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

Cela fonctionne très bien.

Enfin, lorsqu'il s'agit d'opérations réelles dans un programme (sans utiliser l'exécutable sqlite...)

BTW : J'utilise JDBC (pas sûr pour les autres langages)... le pilote sqlite-jdbc v3.7.2 à partir de xerial - Peut-être que des révisions plus récentes changent le comportement expliqué ci-dessous... Si vous développez sous Android, vous n'avez pas besoin d'un pilote jdbc. Toutes les opérations SQL peuvent être soumises à l'aide de la fonction SQLiteOpenHelper .

JDBC dispose de différentes méthodes pour obtenir les valeurs réelles de date et d'heure d'une base de données : java.sql.Date , java.sql.Time et java.sql.Timestamp .

Les méthodes connexes dans java.sql.ResultSet sont (évidemment) getDate(..) , getTime(..) et getTimestamp() respectivement.

Par exemple :

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.

Puisque SQLite n'a pas de type de données DATE/TIME/TIMESTAMP, ces 3 méthodes retournent des valeurs comme si les objets étaient initialisés avec 0 :

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

La question qui se pose est donc la suivante : comment sélectionner, insérer ou mettre à jour des objets de type date/heure/horodatage ? Il n'y a pas de réponse facile. Vous pouvez essayer différentes combinaisons, mais elles vous obligeront à intégrer des fonctions SQLite dans toutes les instructions SQL. Il est beaucoup plus facile de définir une classe utilitaire pour transformer le texte en objets Date dans votre programme Java. Mais n'oubliez jamais que SQLite transforme toute valeur de date en UTC+0000.

En résumé, malgré la règle générale consistant à toujours utiliser le bon type de données, ou même des entiers indiquant le temps Unix (millisecondes depuis l'époque), je trouve beaucoup plus facile d'utiliser le format SQLite par défaut ( '%Y-%m-%d %H:%M:%f' ou en Java 'yyyy-MM-dd HH:mm:ss.SSS' ) plutôt que de compliquer toutes vos déclarations SQL avec des fonctions SQLite. La première approche est beaucoup plus facile à maintenir.

TODO : Je vais vérifier les résultats en utilisant getDate/getTime/getTimestamp dans Android (API15 ou mieux)... peut-être que le pilote interne est différent de sqlite-jdbc...

Answer 5

Habituellement (comme je le fais dans mysql/postgres) je stocke les dates en int(mysql/post) ou text(sqlite) pour les stocker dans le format timestamp.

Ensuite, je les convertirai en objets Date et effectuerai des actions basées sur le TimeZone de l'utilisateur.