Bad date format in SQLite database

Question

I have an issue in Android. There are some classes that saves some data to database. The problem: Sometimes the data in database are in bad format.

E.g., when using "yyyy-MM-dd'T'HH:mm:ssZ" data format, the data in database appears like "2018-03-22T13:43:01+0200".

But sometimes the format is different: "2018-03-22T13:43:0001+0200". Two zero've appeared. After that when trying to get data from database ParseException appears in SimpleCursorParser.java getDate(columnName) mathod

public class DateUtils {

public static final String DATE_FORMAT = "yyyy-MM-dd'T'HH:mm:ssZ";
    /**
     * @return DateFormat in database
     */
    public static DateFormat getDataFormat() {
        return new SimpleDateFormat(DATE_FORMAT, Locale.US);
    }
}

public class DatabaseContentValues {
    private ContentValues mValues;
    private final DateFormat mDateFormat;

    public DatabaseContentValues(int initialSize) {
        mDateFormat = DateUtils.getDataFormat();
        mValues = new ContentValues(initialSize);
    }

    public void putNotNull(String key, Date value) {
    if (value != null)
        mValues.put(key, mDateFormat.format(value));
    }

    public ContentValues getDBValues() {
        return mValues;
    }
}

public class SimpleCursorParser {
     private final Cursor mCursor;
     private DateFormat mDateFormat;

     public SimpleCursorParser(Cursor cursor) {
         mCursor = cursor;
         mDateFormat = DateUtils.getDataFormat();
     }


     public Date getDate(String columnName) {
         final int columnIndex = mCursor.getColumnIndex(columnName);
         if (columnIndex != -1) {
             if (!mCursor.isNull(columnIndex)) {
                 try {
                     return mDateFormat.parse(
                         mCursor.getString(columnIndex)
                      );
                 } catch (ParseException e) {
                     e.printStackTrace();
                 }
             }
         }
         return null;
    }
}

public class SomeDbClass {
    private Date created;

    public SomeDbClass(Cursor cursor) {
        SimpleCursorParser parser = new SimpleCursorParser(cursor);
        created = parser.getDate("created");
    }

    public void setCreated(Date created) {
         this.created = created;
    }

    public ContentValues getDbValues() {
        DatabaseContentValues values = new DatabaseContentValues();
        values.putNotNull(“created”, created);
        return values.getDBValues();
    }

}

public class SomeActivity {
    SomeDbClass dbInfo = new SomeDbClass();
    dbInfo.setCreated(new Date());
    Uri newUri = getContentResolver().insert(someUri, dbInfo.getDbValues());
    Cursor cursor = getContentResolver().query(newUri, null, null, null, null);
    cursor.moveToFirst();
    SomeDbClass infoInDb = new SomeDbClass(cursor);
    infoInDb.getDate(); // <—— when there is bad date format in DB, date value is null here. 
    cursor.close();
}

@Elsunhoty sorry can't do that. All backend should be refactored after this change. — Vytautas Berankis, Apr 24 '18 at 12:02
in your Application Parse Time string To date And Save it in your Sqlite — Elsunhoty, Apr 24 '18 at 12:06
Could it be a multi-threading issue? Could `putNotNull()` be called from two threads at the same time? `SimpleDateFormat` is not thread-safe. As an aside consider throwing away the long outmoded and notoriously troublesome `SimpleDateFormat` and friends, and adding [ThreeTenABP](https://github.com/JakeWharton/ThreeTenABP) to your Android project in order to use `java.time`, the modern Java date and time API. It is so much nicer to work with. And its `DateTimeFormatter` *is* thread-safe. — Ole V.V., Apr 24 '18 at 12:37
as You can see SomeDbClass constructor is using SimpleCursorParser, after using it, it is thrown away. SimpleCursorParser is using new instance of SimpleDateFormat and after using it is thrown away. I know that it is not thread-safe, i don't know if concurrency is the problem. — Vytautas Berankis, Apr 24 '18 at 12:46
edit your question by adding the setDate() method (I suspect that some where you are concatenating instead of adding) — MikeT, Apr 24 '18 at 19:50
@MikeT it appears very rare rarely, with concatenating it will appear every time. — Vytautas Berankis, Apr 25 '18 at 08:06
@OleV.V. thanks for library. I will try to migrate whole project. — Vytautas Berankis, Apr 25 '18 at 08:07
I hope you will be happy with that, @VytautasBerankis. You may find [this question: How to use ThreeTenABP in Android Project](https://stackoverflow.com/questions/38922754/how-to-use-threetenabp-in-android-project) helpful. — Ole V.V., Apr 25 '18 at 08:42
https://i.stack.imgur.com/KItpz.png as you can see now the format are different: 2018-04-20T0008:36:15+0300 device: Samsung galaxy note 4 — Vytautas Berankis, Apr 26 '18 at 08:58

score 0 · Answer 1 · answered Apr 24 '18 at 12:27

0

In table date field take string or text data type at the table created time. then after store date into a particular format. if you want to add date and time both then change format like this

public static final String DATE_FORMAT = "yyyy-MM-dd'T'HH:mm:ss";

answered Apr 24 '18 at 12:27

yes but not understand which format to store data into the database. – Apr 24 '18 at 13:05
this is the format: yyyy-MM-dd'T'HH:mm:ssZ, but as you can see in image https://i.stack.imgur.com/axdJX.png the format somehow is different. It happens very rarely. But need to be fixed. – Vytautas Berankis Apr 24 '18 at 13:08
i test this format and it give value as define in database there for store on data. – Apr 24 '18 at 13:12
you define define format to store date in database i give that format. – Apr 24 '18 at 13:12

Bad date format in SQLite database

1 Answers1