android unix timestamp in sqlite

Question

I have a problem with unix timestamps in sqlite.

I want to store the result of System.currentTimeMillis() in sqllite (integer). But when i read the stored value from the db, i always get a negativ value like -203027418, and not the value, i stored, like 1494445606119

Here is the scheme of my db:

 // Database creation sql statement
private static final String CREATE_ROUTE = "create table "
        + TABLE_ROUTE + "("
        + ROUTE_COLUMN_ID + " integer primary key autoincrement, "
        + ROUTE_COLUMN_DATE + " integer);";

private static final String CREATE_LOCATION = " create table "
        + TABLE_LOCATION + "("
        + LOCATION_COLUMN_ID + " integer primary key autoincrement, "
        + LOCATION_COLUMN_ROUTEID + " integer, "
        + LOCATION_COLUMN_LAT + " real, "
        + LOCATION_COLUMN_LONG + " real, "
        + LOCATION_COLUMN_DATE + " integer, "
        + " FOREIGN KEY(" + LOCATION_COLUMN_ROUTEID + ") REFERENCES " + TABLE_ROUTE + "(" + ROUTE_COLUMN_ID + "));";`

Screenshots from debugging:

At storing, the value seems fine: value in object

but, when i read from db, i only get such values: value after reading from DB

Anyone know, whats happening here?

You might find it much easier and more useful to store date-time values in SQLite as text, using the standard ISO 8601 formats such as 2017-01-23T12:34:56.789Z. — Basil Bourque
– Basil Bourque, Commented May 10, 2017 at 23:54

John Smith · Accepted Answer · 2017-05-10 20:07:35Z

1

It happens because the SQLite expects a timestamp in seconds, but not milliseconds. When you try to store the timestamp in milliseconds, overflow happens and the SQLite saves negative integer instead of timestsamp.

answered May 10, 2017 at 20:07

John Smith

6054 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Julian Over a year ago

i also tried locationObject.setDate(System.currentTimeMillis()/1000); , but i got the same results.

John Smith Over a year ago

try locationObject.setDate((int) (System.currentTimeMillis()/1000));

MikeT Over a year ago

I don't believe that the issue is that SQLite expects a timestamp in seconds. I believe the issue is to do with a timestamp being a long and that it is either being stored or retrieved as an int, outside of SQLIte. I have no problems storing timestamps.

Julian Over a year ago

ok, the int cast, as suggested by @JohnSmith seems to do the trick, thank you very much!

Julian Over a year ago

@MikeT: yes, outside of sqlite i handle it as a long, but AFAIK, sqlite doesn't have an explicit type for long. May i ask how you handle something like this?

|

MikeT · Accepted Answer · 2017-05-10 21:32:05Z

An example of storing (updating) a timestamp (as requested by Julian).

        long newdate = adjustcal.getTimeInMillis();
        ContentValues cv = new ContentValues();
        cv.put(DBRulesTableConstants.RULES_ACTON_COL,newdate);
        String whereclause = DBRulesTableConstants.RULES_ID_COL_FULL + " = ?";
        String whereargs[] = new String[] { Long.toString(ruleid)};
        updatecount = db.update(DBRulesTableConstants.RULES_TABLE,cv,whereclause,whereargs);

This gets the newly adjusted timestamp from adjustcal (Calendar object) as a long (with millsecs) and then updates the ACTON column for the respective RULE.

Here's an example of extracting the ACTON column (as used in a CursorAdapter for a ListView)

nextdate.setText(sdf.format(csr.getLong(rules_ruleacton_offset)));

sdf is a SimpleDateFormat. Note the use of the getLong() method, an SQLite Cursor method.

This is the code I use for inserting a Rule (ACTON is the 6th paramter, also note that the productid and aisleid are also longs (references to other _id columns of related tables).

    void insertRule(long productid,
                    long aisleid,
                    String rulename,
                    int numbertoget,
                    boolean promptflag,
                    long actondate,
                    int period,
                    int multiplier) {

        long addedid;
        lastruleaddok = false;

        ContentValues cv = new ContentValues();
        cv.put(DBRulesTableConstants.RULES_PRODUCTREF_COL,productid);
        cv.put(DBRulesTableConstants.RULES_AISLEREF_COL,aisleid);
        cv.put(DBRulesTableConstants.RULES_NAME_COL,rulename);
        cv.put(DBRulesTableConstants.RULES_USES_COL,numbertoget);
        cv.put(DBRulesTableConstants.RULES_PROMPT_COL,promptflag);
        cv.put(DBRulesTableConstants.RULES_ACTON_COL,actondate);
        cv.put(DBRulesTableConstants.RULES_PERIOD_COL,period);
        cv.put(DBRulesTableConstants.RULES_MULTIPLIER_COL,multiplier);
        addedid = db.insert(DBRulesTableConstants.RULES_TABLE,
                null,
                cv);
        if (addedid >= 0) {
            lastruleadded = addedid;
            lastruleaddok = true;
        }
    }

Here's a screenshot of the Rules table (note the column name is actually ruleacton rather than ACTON as used above). This shows the timestamps (note I purposefully set millisecs and seconds to 0 as in this case I'm only interested in the date).

Collectives™ on Stack Overflow

android unix timestamp in sqlite

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related