SQLiteDatabase add date into database

Question

I have a class extends SQLiteOpenHelper and a database declared as:

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + COL_TEMPERATURA
            + " TEXT, " + COL_UMIDITA + " TEXT," + COL_DATA + "DATETIME)");
}

now i would insert records inside it, but when i try to insert the last field (of DATETIME type) eclipse told me that i can't do it:

public void insertRecord(List<Object[]> list) throws ParseException {
    String temperatura, umidita, data;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();

    for (int i = 0; i < list.size(); i++) {
        temperatura = String.valueOf(list.get(i)[0]);
        umidita = String.valueOf(list.get(i)[1]);
        data = String.valueOf(list.get(i)[2]);
        // parsing data
        DateFormat dateformat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
        Date data_date = dateformat.parse(data);
        // insert into database
        cv.put(COL_TEMPERATURA, temperatura);
        cv.put(COL_UMIDITA, umidita);
        cv.put(COL_DATA, data_date);
        db.insert(TABLE_NAME, null, cv);
        db.close();
    }

because cv.put() takes as arguments only two strings. Now if i parse data_date variable into string, MySql automatically convert it in its correct format?

SQLite doesnt have a DATETIME format: http://www.sqlite.org/datatype3.html - instead try the following date scheme: http://stackoverflow.com/questions/1933720/how-do-i-insert-datetime-value-into-a-sqlite-database — x29a, Sep 09 '13 at 15:22

score 3 · Answer 1 · answered Sep 09 '13 at 15:20

3

you cannot insert objects into SQLite you can only enter Integers, Text, Blobs, Real or Numeric

so you need to send the date in as a string or better yet make the date as a timestamp and store the timestamp so its easier to process stuff

answered Sep 09 '13 at 15:20

tyczj

71,600
54
194
296

hendrix · Accepted Answer · 2013-09-10T05:55:05.787

3

SQLite doesnt support DateTime format. Instead, i recommend you to use string in ISO8601 format:

"YYYY-MM-DD HH:MM:SS.SSS"

Read more about it here

Using this format of course supports ordering. I prefer this string format, because its much more readable. If you choose integer format (number of milliseconds since 1970-01-01) you will not get the same readability without parsing involved...

edited Sep 10 '13 at 05:55

answered Sep 09 '13 at 15:21

hendrix

3,364
8
31
46

subsequently i perform a select query on database, and i need records ordered ascending by date. If i store date in this ISO format, could i have issue with that query? – giozh Sep 09 '13 at 15:25
@giozh just save the date as a timestamp, it will be much easier to deal with – tyczj Sep 09 '13 at 15:32
@tyczj i dont see how using timestamp will be easier to deal with than ISO8601 string. – hendrix Sep 10 '13 at 05:57
@giozh of course you can order by date when you use this format. Simply use ORDER BY as you would on any other column, it will work. See edited answer. – hendrix Sep 10 '13 at 05:58
1

@giozh its easier because you have just a number and not a formatted date and you dont have to worry about timezone issues when storing – tyczj Sep 10 '13 at 13:24

SQLiteDatabase add date into database

2 Answers2