how to insert data type date(yyyy-MM-dd) in sqlite

问题:

I have a Sqlite3 database table contains name,address,date of birth details.i want to display 1990-01-01 to 1995-01-01 details.

but Sqlite3 database stores only following data types.

TEXT
NUMERIC
INTEGER
REAL
NONE

Any one have some hint to store and retrieve date format data..?

回答1:

Use this code to convert your date into millisecond format and store it into your database as INTEGER types

String someDate = "1995-01-01";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf.parse(someDate);
System.out.println(date.getTime());

date.getTime()-give the millisecond format

At the same way to convert your input (i.e from 1990-01-01 and to date 1995-01-01)

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date date1 = sdf.parse(1990-01-01);
    value1=date.getTime();
Date date2 = sdf.parse(1995-01-01);
    value2=date.getTime();

Retrieve from database using following query

db.rawQuery("SELECT * FROM table_name WHERE column_name BETWEEN "+value1+" AND "+value2+"",null);

or 

db.rawQuery("SELECT * FROM table_name WHERE column_name<="+value1+" AND column_name>="+value2+"",null);

回答2:

From my own experience on doing several projects with database in Android my answer is:

Do not store the date as a string. Never! Ever! Store them as Unix timestamps and format them as needed during runtime.

the important thing here is to separate what is your data and what is the on-screen representation of your data. Storing in a database the on-screen representation of your data is wrong.

You'll always store your dates as INTEGER types.

So for example to store the date now you'll store the value System.currentTimeInMilis

To select between 1990-01-01 and 1995-01-01 you will:

long val1 = new GregorianCalendar(1990, 01, 01).getTimeInMillis();
long val2 = new GregorianCalendar(1995, 01, 01).getTimeInMillis();

and then you'll do the normal SELECT statement between those 2 values.

to show those values in the screen as yyyy-MM-dd you'll use the SimpleDateFormat class:

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
long longDate = cursor.getLong(colNumber); // from the database
String stringDate = dateFormat.format(new Date(longDate));

回答3:

You can do something like this

DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
    Date date1=df.parse("1990-01-01");
    Date date2=df.parse("1995-01-01");
    Date myDate=df.parse("1992-01-01"); // checking date

    if((date1.getTime()<myDate.getTime())&&(myDate.getTime()<date2.getTime())){
        System.out.println(df.format(myDate)+" is in this range");
    }else{
        System.out.println(df.format(myDate)+" is not in this range");
    }

回答4:

Since the format you want to use (yyyy-MM-dd) is ordered in the same way as a String (i.e. for any dates x and y you would choose, if x < y as a Date, then x < y as a String), you can simply store the dates as Strings (TEXT) in your database.

When selecting the values between them, you would just have to use a WHERE clause in your SELECT statement like this:

 SELECT * FROM yourTable WHERE yourDateFieldName > ? and yourDateFieldName < ?

You can then use DateFormat.format to set the values for the ? parameters of your prepared statement. The first parameter would be the "start" date, and the second would be the "end" date. You can replace < with <= and > with >= if you want the items on start and end dates included.

This gives you a String representation of a Date. To convert from that to an actual Date object you can use date formatter's parse method (i.e. SimpleDateFormat.parse).

Another, "cleaner", approach would be to use the SQLite date and time functions (see here). While SQLite doesn't have a DATE type for storing date values, it has helper functions that you can use to interpret TEXT and NUMBER values as date in your statements.

If you don't need extra processing for your date values, I'd recommend going for the first solution as it should be faster because it merely compares TEXTs rather than parsing and extracting a date from them, then comparing the extracted date (I haven't compared the speed of the two approaches, so don't take my word for it on this one). This approach also has less code to write and maintain and the code is easier to read.

Sources:

SQLite data type - for the validity of comparing two TEXT values

SimpleDateFormat - Android documentation

回答5:

You can use dates in yyyy-MM-dd format directly, JDBC will understand it. Assuming we a have a table t1 with c1 of DATE type

PreparedStatement ps = conn.prepareStatement("insert into t1 (c1) values (?)");
ps.setString(1, "2001-01-01");
ps.executeUpdate();

Reading dates is simple too

ResultSet rs = st.executeQuery("select c1 from t1");
rs.next();
Date date = rs.getDate(1);

ResultSet.getDate returns result as java.sql.Date whose toString method returns date in yyyy-MM-dd format