CODEWITHSUNDEEP
javasqlite
roast_soul
roast_soul

Reputation: 3650

Sqlite Datetime Type Issue

I try to insert a datetime into a sqlite DB from Java, but things are weird.

When I use the code Calendar.getInstance().getTime() to insert, the value in table is (for example) 1359960520718. When I use the the string 2010-01-01 to insert into the table, the value is normal: 2010-01-01.

But when I use the string 2010-21-51 to insert into the table, the value in the table is 2010-21-51. That's an invalid date. What's wrong with it?

Upvotes: 1

Views: 5825

Answers (2)

bleedcloud
bleedcloud

Reputation: 21

you can new a date of Date type with the param from Calendar.getInstance().getTime(),like:

Date date = new Date(Calendar.getInstance().getTime());

and then,maybe you shold use SimpleDateFormate to formate the date as a String you need,like:

DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
String timeStr = df.format(date);

and then,you an insert the timeStr into your table... you also have other methods if you do something in your insert statement of your database

Upvotes: 1

mvp
mvp

Reputation: 116177

This is because SQLite is dynamically typed. You can write value of any type into any column, and it won't complain - it may silently store it in different type.

According to documentation, datetime can be stored as

  • 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.

In your case, SQLite was unable to convert invalid date into INTEGER format, and was forced to store it as standard string instead. This date is invalid, but SQLite does not care.

Upvotes: 8

Related Questions