How to insert date in sqlite through java

Question

I want to make a database that will hold a date in it(SQLite). Now first to ask is what is the right syntax to declare a date column. The second i want to know is how to insert date in it after that. And the third thing i want to know is how to select dates between, for example to select all rows which contain date between 01/05/2010 and 05/06/2010.

Thank you

Answer 1

Now first to ask is what is the right syntax to declare a date column.

From the SQLite Data Types documentation:

1.2 Date and Time Datatype

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.

Take your pick. I'd go for TEXT or INTEGER. The INTEGER will be faster. If you need to store dates past 1970 (e.g. birthdates, etc), then I'd go for TEXT. If you just need to store creationtime/modificationtime, etc for now and the future, then go for INTEGER.

The second i want to know is how to insert date in it after that.

Use PreparedStatement#setString() or #setLong() respectively.

And the third thing i want to know is how to select dates between, for example to select all rows which contain date between 01/05/2010 and 05/06/2010.

Use the standard SQL BETWEEN clause for this. You first need to convert the date accordingly using the built-in date and time functions.

Answer 2

A convenient syntax for declaring a date column is like this:

"CREATE TABLE "
            + "my_table"
            + "(date_time " 
            + " DATETIME DEFAULT CURRENT_TIMESTAMP);");

This will default inserted values to the current datetime. Or you can use CURRENT_DATE, or CURRENT_TIME values. You won't have to insert a timestamp manually each time you create a row.

You can read about this approach here: Android insert datetime value in SQLite database