Best way to work with dates in Android SQLite

Question

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions:

What type should I use to store dates in SQLite (text, integer, ...)?
Given the best way to store dates how do I store It properly using ContentValues?
What's the best way to retrieve the date from the SQLite database?
How to make a sql select on SQLite, ordering the results by date?

Just use the Calendar class and its member time (which represents the number of milliseconds that have passed since 1/1/1970). There are member functions for mutating the time value into user readable strings. — slayton, Sep 09 '11 at 14:28

score 217 · Answer 1 · edited Apr 14 '17 at 06:00

The best way is to store the dates as a number, received by using the Calendar command.

//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());

Why do this? First of all, getting values from a date range is easy. Just convert your date into milliseconds, and then query appropriately. Sorting by date is similarly easy. The calls to convert among various formats are also likewise easy, as I included. Bottom line is, with this method, you can do anything you need to do, no problems. It will be slightly difficult to read a raw value, but it more than makes up that slight disadvantage with being easily machine readable and usable. And in fact, it is relatively easy to build a reader (And I know there are some out there) that will automatically convert the time tag to date as such for easy of reading.

It's worth mentioning that the values that come out of this should be long, not int. Integer in sqlite can mean many things, anything from 1-8 bytes, but for almost all dates 64 bits, or a long, is what works.

EDIT: As has been pointed out in the comments, you have to use the cursor.getLong() to properly get the timestamp if you do this.

Thanks guy. Lol I thought of a mis-typing but I couldn't find it. It must be retrieved by cursor.getLong(), not by cursor.getInt(). Lol can not stopping laughing at myself. Thanks again. — Son Huy TRAN, Dec 10 '13 at 16:35
You write "It's worth mentioning that the values that come out of this should be long, not int. " but at the same time you use 'int' in your query. So on the one hand you are saying use longs and not integers and on the other hand you use integers and not longs. This is fairly confusing. — VanessaF, Sep 25 '20 at 09:27
As @VanessaF said, you need to use a long working with DateTime in ms; otherwise, you risk an overflow of the int causing incorrect dates. — theZ3r0CooL, Feb 01 '22 at 15:37
There are 4 types of values in SQLite, integers, real, strings, and blobs. Long isn't a type, but it is a subset of integers. When you get the value from it, as mentioned in the last line, you have to use "cursor.getLong()", but when you declare it in the table, it has to be declared as an integer. — PearsonArtPhoto, Feb 01 '22 at 16:14

score 40 · Accepted Answer · edited Apr 13 '17 at 11:56

40

You can use a text field to store dates within SQLite.

Storing dates in UTC format, the default if you use datetime('now') (yyyy-MM-dd HH:mm:ss) will then allow sorting by the date column.

Retrieving dates as strings from SQLite you can then format/convert them as required into local regionalised formats using the Calendar or the android.text.format.DateUtils.formatDateTime method.

Here's a regionalised formatter method I use;

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

edited Apr 13 '17 at 11:56

Kapil Rajput

11,429
9
50
65

answered Sep 09 '11 at 14:55

CodeChimp

4,745
6
45
61

66

How would you handle querying date ranges? – Joe Jan 21 '12 at 06:25
53

"Recommended practice"? Doesn't sound right. – shim Nov 21 '12 at 20:32
137

In the years I've been using SQL I've never seen anyone previously recommend storing dates as strings. If you don't have a specific date column type, use an integer and store in Unix time (seconds since the epoch). Its sortable and usable in ranges and easily converted. – mikebabcock Nov 28 '12 at 15:44
1

Storing dates as formatted strings are fine, if you do not need high precision. In general storing dates as unix milliseconds can help in that situation. – C-- Jan 11 '13 at 17:48
22

Storing dates as string is fine if you want to store it as "information", something you retrieve and show. But if you want to store dates as "data", something to work with, you should consider storing it as integer - time since epoch. This will allow you to query date ranges, it's standard so you don't have to worry about conversions etc etc. Storing dates as string is very limiting and I would really like to know who recommended this practice as a general rule. – Krystian Apr 17 '13 at 18:56
3

One problem with storing as milliseconds, for example is that you lose the timzezone. So you can save that separately. Knowing the instant in time is not the only important thing. Knowing what day/time the user perceives it as is often important if you are going to display the information back to them. – Fraggle May 30 '13 at 15:51
1

@Joe you can actually easily sort it with < or > operators I guess. String < and > operations are working for date strings as well. – tasomaniac Jul 24 '13 at 07:10
8

The [sqlite documentation](http://www.sqlite.org/datatype3.html) lists storing as text (ISO 8601) as a viable solution for storing dates. Actually, it is listed first. – anderspitman Aug 01 '14 at 01:14
5

viable doesn't mean recommended. – johnw182 Sep 21 '14 at 19:22
4

@anderspitman read the comment of mikebabcock. He has stated why storing as int/long is better than string. This answer is way off the reality. I wonder why it has received so many upvotes. – Sufian Oct 13 '15 at 07:49
1

@tasomaniac int/long comparison is lighter than its string counterpart. Why would you do something which is heavy on resources? And why would you do something which is not a common practice either? – Sufian Oct 13 '15 at 07:59
I vote for https://stackoverflow.com/a/13694823/689956 to be the accepted answer. Storing date in string values is not an engineering best practice. – Sathesh Aug 22 '20 at 19:57
I do not like this approach as you can't query the entries. – VanessaF Sep 25 '20 at 09:28

score 38 · Answer 3 · edited May 23 '17 at 11:54

As presumed in this comment, I'd always use integers to store dates.

For storing, you could use a utility method

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

like so:

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

Another utility method takes care of the loading

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

can be used like this:

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

Ordering by date is simple SQL ORDER clause (because we have a numeric column). The following will order descending (that is newest date goes first):

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
    }

Always make sure to store the UTC/GMT time, especially when working with java.util.Calendar and java.text.SimpleDateFormat that use the default (i.e. your device's) time zone. java.util.Date.Date() is safe to use as it creates a UTC value.

miguelt · Answer 4 · 2014-03-14T01:51:24.777

SQLite can use text, real, or integer data types to store dates. Even more, whenever you perform a query, the results are shown using format %Y-%m-%d %H:%M:%S.

Now, if you insert/update date/time values using SQLite date/time functions, you can actually store milliseconds as well. If that's the case, the results are shown using format %Y-%m-%d %H:%M:%f. For example:

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

Now, doing some queries to verify if we are actually able to compare times:

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

You can check the same SELECT using col2 and col3 and you will get the same results. As you can see, the second row (126 milliseconds) is not returned.

Note that BETWEEN is inclusive, therefore...

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

... will return the same set.

Try playing around with different date/time ranges and everything will behave as expected.

What about without strftime function?

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

What about without strftime function and no milliseconds?

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

What about 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

Works just fine.

Finally, when dealing with actual operations within a program (without using the sqlite executable...)

BTW: I'm using JDBC (not sure about other languages)... the sqlite-jdbc driver v3.7.2 from xerial - maybe newer revisions change the behavior explained below... If you are developing in Android, you don't need a jdbc-driver. All SQL operations can be submitted using the SQLiteOpenHelper.

JDBC has different methods to get actual date/time values from a database: java.sql.Date, java.sql.Time, and java.sql.Timestamp.

The related methods in java.sql.ResultSet are (obviously) getDate(..), getTime(..), and getTimestamp() respectively.

For example:

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.

Since SQLite doesn't have an actual DATE/TIME/TIMESTAMP data type all these 3 methods return values as if the objects were initialized with 0:

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

So, the question is: how can we actually select, insert, or update Date/Time/Timestamp objects? There's no easy answer. You can try different combinations, but they will force you to embed SQLite functions in all the SQL statements. It's far easier to define an utility class to transform text to Date objects inside your Java program. But always remember that SQLite transforms any date value to UTC+0000.

In summary, despite the general rule to always use the correct data type, or, even integers denoting Unix time (milliseconds since epoch), I find much easier using the default SQLite format ('%Y-%m-%d %H:%M:%f' or in Java 'yyyy-MM-dd HH:mm:ss.SSS') rather to complicate all your SQL statements with SQLite functions. The former approach is much easier to maintain.

TODO: I will check the results when using getDate/getTime/getTimestamp inside Android (API15 or better)... maybe the internal driver is different from sqlite-jdbc...

Given the internal storage engine of SQLite, I am not convinced that your examples have the effect you imply: It looks like the engine "allows to storing any storage-typed values in any column irrespective of the declared SQL type" (https://books.google.de/books?id=9Z6IQQnX1JEC&pg=PA200&lpg=PA200). It sounds to me that in your Real vs. Integer vs. Text example, what's happening is this: SQLite just stores the text as Text in all of the tree columns. So, naturally the results are all good, storage still wasted. If just an Integer was used, then you should loose milliseconds. Just saying... — marco, Sep 07 '15 at 13:46
In fact, you can confirm what I just said by doing a SELECT datetime(col3, 'unixepoch') FROM test_table. This will show empty rows for your examples... unless, for test's sake, you insert an actual Integer. For example, if you were to add a row with col3 value 37, the SELECT statement above will show: 1970-01-01 00:00:37. So, unless you are actually fine with storing all your dates rather inefficiently as text string, don't do as you suggest. — marco, Sep 07 '15 at 13:58
Has been a long time since I posted this answer... maybe SQLite has been updated. Only thing I can think of is to execute the SQL statements again vs. your suggestions. — miguelt, Sep 14 '15 at 15:29

score 3 · Answer 5 · answered Dec 20 '17 at 07:04

Best way to store datein SQlite DB is to store the current DateTimeMilliseconds. Below is the code snippet to do so_

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

Insert the data in your DB

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 .

Next step is, when you want to retrieve data from DB you need to convert the respective date time milliseconds in to corresponding date. Below is the sample code snippet to do the same_

Convert date milliseconds in to date string.

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

Now, Finally fetch the data and see its working...

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;

}

Hope this will help all! :)

SQLite does not support the long datatype. EDIT: My mistake, INTEGER is 8 byte long, so it should support this datatype. — Antonio Vlasic, May 08 '18 at 09:53

score 3 · Answer 6 · answered Sep 09 '11 at 15:03

3

Usually (same as I do in mysql/postgres) I stores dates in int(mysql/post) or text(sqlite) to store them in the timestamp format.

Then I will convert them into Date objects and perform actions based on user TimeZone

answered Sep 09 '11 at 15:03

StErMi

5,389
5
48
71

lidox · Answer 7 · 2016-06-16T06:11:38.373

I prefer this. This is not the best way, but a fast solution.

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

score 1 · Answer 8 · edited May 21 '14 at 04:15

1

1 -Exactly like StErMi said.

2 - Please read this: http://www.vogella.de/articles/AndroidSQLite/article.html

3 -

Cursor cursor = db.query(TABLE_NAME, new String[] {"_id", "title", "title_raw", "timestamp"}, 
                "//** YOUR REQUEST**//", null, null, "timestamp", null);

see here:

Query() in SQLiteDatabase

4 - see answer 3

edited May 21 '14 at 04:15

Pratik Butani

60,504
58
273
437

answered Sep 09 '11 at 15:07

Waza_Be

39,407
49
186
260

score 0 · Answer 9 · edited Dec 17 '17 at 20:25

0

"SELECT  "+_ID+" ,  "+_DESCRIPTION +","+_CREATED_DATE +","+_DATE_TIME+" FROM "+TBL_NOTIFICATION+" ORDER BY "+"strftime(%s,"+_DATE_TIME+") DESC";

edited Dec 17 '17 at 20:25

Brandon Minnick

13,342
15
65
123

answered Jun 21 '16 at 09:37

Suresh Mewara

129
5

Best way to work with dates in Android SQLite

9 Answers9

Linked

Related