CODEWITHSUNDEEP
androiddatabasesqlitedate
Kostya Khuta
Kostya Khuta

Reputation: 1856

How to add date in sqlite database

I have sqlite database

    private static final String DB_PROCESS_CREATE = "create table "
        + DB_TABLE_PROCESS + "(" + PROCESS_ID
        + " integer primary key autoincrement, "
        + PROCESS_DATE + " date);";

I create it db.execSQL(DB_PROCESS_CREATE);

How to add date value in this database? I tried :

String date = new SimpleDateFormat("yyyy.MM.dd").format(Calendar
.getInstance().getTime());
ContentValues cv = new ContentValues();
cv.put(db.PROCESS_DATE,Date.valueOf(date));
db.mDB.insert(db.DB_TABLE_PROCESS, null, cv));

But then I get error :

"The method put(String, String) in the type ContentValues is not applicable for the arguments (String, Date)".

Upvotes: 24

Views: 74898

Answers (3)

Homam
Homam

Reputation: 5076

Now when you want to insert date to database, you can use this code.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String date = sdf.format(new Date());

In database insert the string 'date'

The date format in sqlite should be of following format:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

For more details, have a look: http://www.sqlite.org/lang_datefunc.html

Upvotes: 54

Ilya Lysenko
Ilya Lysenko

Reputation: 1892

You cannot store date in SQLite directly. For example, you can store it as integer value:

ContentValues cv = new ContentValues();
cv.put(db.PROCESS_DATE, new Date().getTime());
db.mDB.insert(db.DB_TABLE_PROCESS, null, cv));

In this case your date field must be declared as integer:

private static final String DB_PROCESS_CREATE = "create table "
        + DB_TABLE_PROCESS + "(" + PROCESS_ID
        + " integer primary key autoincrement, "
        + PROCESS_DATE + " integer);";

From SQLite docs: SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  1. TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  2. REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  3. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

See http://www.sqlite.org/datatype3.html

Upvotes: 14

Martin Estes
Martin Estes

Reputation: 26

The best way to put a date into a database would be using the long value of it. I tried to do what you were doing, and it had a similar error. Instead of a string, put in a numerical value. It can take the long value, which I believe is the millisecond value of some sort. It can then be reconverted when you pull it out.

Upvotes: 2

Related Questions