How to add date in sqlite database

Question

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)".

Similar: [*android adding or removing hour from timestamp in database*](https://stackoverflow.com/q/13093016/642706) — Basil Bourque, Nov 16 '18 at 21:27

score 52 · Accepted Answer · edited Mar 13 '14 at 12:53

52

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

edited Mar 13 '14 at 12:53

GVillani82

17,196
30
105
172

answered May 24 '13 at 16:56

Homam

5,018
4
36
39

I tried it, but in this case it is impossible to make sort by date – Kostya Khuta May 24 '13 at 17:05
@KostyaKhuta Try this, this should work. Let the type PROCESS_DATE be date. – Homam May 24 '13 at 17:12
"The constructor Date() is undefined" What i must to transmit to constructor – Kostya Khuta May 24 '13 at 17:18
Press Ctrl+Shit+O to import missing packages automatically OR import java.util.Date; – Homam May 24 '13 at 17:19
My boner, in my class i import import java.sql.Date=) – Kostya Khuta May 24 '13 at 17:22
@KostyaKhuta :D new Date() will give you the current date – Homam May 24 '13 at 17:28
Yes, it is a solution. Thank you a lot. Good luck – Kostya Khuta May 24 '13 at 17:29

Ilya Lysenko · Answer 2 · 2013-05-24T19:11:40.060

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:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
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.
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

score 2 · Answer 3 · answered May 24 '13 at 16:45

2

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.

answered May 24 '13 at 16:45

Martin Estes

26
4

i think about it, but than i will found records by date, and it will be hard if i store date like long value. – Kostya Khuta May 24 '13 at 16:47
You could pull the values and check them then, but that's a bit inefficient. You could make another table of just one part of the date, like the day, and search through that way. Another problem with your code might be that you are doing Date.valueOf() instead of just storing date. Try that instead. Valueof is actually returning a date data type while your database wants a string. – Martin Estes May 24 '13 at 16:51
it is always better to store the long value in DB when storing a date in a database as the long value is always UTC. This gives you a chance to always show the date to the user in their current timezone. – Sam May 24 '13 at 16:52
ok, but how i will find record by date? if i don't know tine, i only know date of record – Kostya Khuta May 24 '13 at 16:59
You could pull out all of the dates and loop through them as long as there wont be thousands of them. – Martin Estes May 24 '13 at 17:02
Martin Estes, if i understand, i must create table with three colums: day, mounth, year? But how to sort records by date? – Kostya Khuta May 24 '13 at 17:02
As long as you aren't adding dates before the current date, it shouldn't have to. If you aren't going to have overlapping dates, use a treeset. Otherwise, you'll need to overwrite compareTo to accommodate years, months, and days. I'd suggest one of the first two. – Martin Estes May 24 '13 at 17:05
hmm..but i can make sort by date (long value), and after that make convert to date format – Kostya Khuta May 24 '13 at 17:12
Yes, you can do that. Date values hold the exact second, so they will always be different unless you somehow grab two dates literally at the same time. – Martin Estes May 24 '13 at 17:14
Sorting by dates is as simple as sorting the long values. The long values represent the milliseconds elapsed from 1st Jan 1990. If you want fields with the current date just make a query to fetch rows with long values greater than or equal to 24th May 2013 00:00 and less than 25th May 2013 00:00. – Sam May 24 '13 at 17:16

How to add date in sqlite database

3 Answers3