19

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

Improve this question
3
  • Do you really need sqlite? hsqldb.org may be a more convenient for using from java. Commented May 21, 2010 at 11:08
  • Well i kinda want to have expirience with SQLite 'cos it is really good. Commented May 21, 2010 at 11:29
  • See also Java Date - Insert into database. Commented Jul 3, 2015 at 19:06

2 Answers 2

Reset to default
36

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

will this be correct sql statement: INSERT INTO table (name, date) VALUES ("Jack", date("05/10/2010")) and SELECT * FROM table WHERE date BETWEEN date("05/10/2010") and ("05/11/2010") and before that i created the columns name and date as a varchars.
Click the link the built-in date and time functions. There you'll see that a date format of YYYY-MM-DD is required.
So if i change the / with - all other would be correct statement?
No. You need to shuffle the dateparts as well. YYYY stands for years. MM stands for months. DD stands for days.
4

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

Improve this answer

2 Comments

while this may not what the OP asked for, it's a better alternative for simple use cases (when you don't need to create a specific date which is not "now"). got my vote!
@Kepedizer What DID the OP ask for??

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.