CODEWITHSUNDEEP
androidsqlitedatetime
Mert Serimer
Mert Serimer

Reputation: 1245

Android Sqlite datetime and strftime all returns wrong dates

My table structure is like this:

protected static final String EXPENSE_TABLE = "Expense";
protected static final String EXPENSE_ID = "_id";
protected static final String EXPENSE_AMOUNT= "Amount";
protected static final String EXPENSE_CATEGORY ="Category";
protected static final String EXPENSE_SUBCATEGORY ="SubCategory";
protected static final String EXPENSE_REASONCATEGORY ="ReasonCategory";
protected static final String EXPENSE_DATE ="Date";
protected static final String EXPENSE_EVENT ="Event";
protected static final String CREATE_EXPENSE_TABLE = "CREATE TABLE "+ EXPENSE_TABLE +"(" +
        ""+ EXPENSE_ID +" INTEGER PRIMARY KEY AUTOINCREMENT," +
        ""+ EXPENSE_AMOUNT +" REAL NOT NULL ," +
        ""+ EXPENSE_CATEGORY +" VARCHAR(50) NOT NULL," +
        ""+ EXPENSE_SUBCATEGORY +" VARCHAR(50)  NULL," +
        ""+ EXPENSE_REASONCATEGORY +" VARCHAR(50) NULL ," +
        ""+ EXPENSE_DATE +" DATE NOT NULL," +
        ""+ EXPENSE_EVENT +" VARCHAR(50)" +
        ");";

My insert query:

 String sql = "INSERT INTO "+DbHelper.EXPENSE_TABLE +"" +
            "("+DbHelper.EXPENSE_AMOUNT +","+DbHelper.EXPENSE_CATEGORY +","+DbHelper.EXPENSE_SUBCATEGORY+"," +
            " "+DbHelper.EXPENSE_REASONCATEGORY +","+DbHelper.EXPENSE_DATE+","+DbHelper.EXPENSE_EVENT+") " +
            "values(?,?,?,?,?,?)";
    db.beginTransaction();
    SQLiteStatement stmt = db.compileStatement(sql);

    stmt.bindDouble(1, e.getAmount());
    stmt.bindString(2,e.getCategory());
    stmt.bindString(3,e.getNote());
    stmt.bindString(4, e.getReasonCategory());
    stmt.bindLong(5, e.getDate());
    stmt.bindString(6,e.getEvent());

    e.setId(stmt.executeInsert());
    stmt.clearBindings();

    db.setTransactionSuccessful();
    db.endTransaction();
    return e.getId();

When I insert to this table, I insert long value there. Below you can find my insert query. As you see, I bind long for dates. When I create the table, the field is created as date, but I insert it as long, which is fine as I found online. But the results (when I use that sqlite functions) coming from sqlite are either obscure or null, such as 24-11--471 11:59:59 or '01-01-1970' etc. When I convert long fields with SimpleDateFormat it looks fine. Result is true. So since sqlite interprets long wrong, I cannot write complex queries.

Upvotes: 1

Views: 1070

Answers (2)

Phillip Kigenyi
Phillip Kigenyi

Reputation: 1489

The database storage can get alittle hairy. In my view, store the data as strings then cast it back into long or int(what ever type you want)

OR

Use this method to get the date from the android system. And then store it in the database

@SuppressLint("SimpleDateFormat")
private static String getTime() {
    //for date only
    String pattern = "yyyy-MM-dd";
    //or the one below for time and date
    String pattern2 = "yyyy-MM-dd HH:mm:ss ";
    SimpleDateFormat formatter = new SimpleDateFormat(pattern);
    String key = formatter.format(new Date());
    return key;
}

Upvotes: 0

Mert Serimer
Mert Serimer

Reputation: 1245

  strftime('%Y-%m', milliseconds / 1000, 'unixepoch') did the trick

Dividing bby /1000 and adding 'unixepoch' fixed it. What i was looking for is long to date conversion actually

Upvotes: 1

Related Questions