How to store the time only in SQLite?

Question

How to store the time in SQLite?

I have use the time as a static final value. I was stuck on inserting and retrieving the time from and to SQLite.

I referred to some sites in Google:

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.

But I am confused.
And I am getting this error.

Caused by: android.database.sqlite.SQLiteException: near "From": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS proname(ImageID INTEGER,Title TEXT,From INTEGER,To INTEGER); at android.database.sqlite.SQLiteConnection.nativePrepareStatem‌​ent(Native Method)

public static final String[] titles = new String[]{"Akilam 360","Ipadikku Idhayam",
        "Palsuvai Thoranam"};

public static  final String[] fromtime = new String[]{"05:00:00","07:00:00","09:00:00"};

public static  final String[] totime = new String[]{"07:00:00","09:00:00","11:00:00"};


public static final Integer[] images = {R.drawable.akilam_360,
        R.drawable.ipadikku_idhayam, R.drawable.palsuvai_thoranam};

ListView listView;
List<Program> rowItems;
int  iImageId;
String sTitle,sFrom,sTo ;
SQLiteDatabase db;


@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.dbcon);

    Calendar c = Calendar.getInstance();

    int seconds = c.get(Calendar.SECOND);
    int minutes = c.get(Calendar.MINUTE);
    int hour = c.get(Calendar.HOUR_OF_DAY);
    String time = hour+":"+minutes+":"+seconds;

    timer = (TextView) findViewById(R.id.timer);
    timer.setText(time);


    db =openOrCreateDatabase("MukilProgram", Context.MODE_PRIVATE,null);
    db.execSQL("CREATE TABLE IF NOT EXISTS proname(ImageID INTEGER,Title TEXT,From TEXT);");

    rowItems = new ArrayList<Program>();
    for (int i = 0; i < titles.length; i++) {
        Program item = new Program(images[i], titles[i],fromtime[i],totime[i]);
        rowItems.add(item);
    }


    db.execSQL("DELETE FROM proname;");

    listView = (ListView) findViewById(listview);
    final ProgramAdapter adapter = new ProgramAdapter(this,rowItems, false);
    listView.setAdapter (adapter);


    for (int i = 0; i < adapter.getCount(); i++) {
        Program rowItem = (Program) adapter.getItem(i);

            iImageId = rowItem.getImageId();
            sTitle = rowItem.getTitle();
            sFrom = rowItem.getFromtime();
            sTo  = rowItem.getTotime();


            //sQuantity = rowItem.getQuantity();
           db.execSQL("INSERT INTO proname VALUES("+ iImageId + ",'" + sTitle + "','"+sFrom+"','"+sTo+"');");
            Toast.makeText(Databaseconnection.this, "Added to the Table..", Toast.LENGTH_SHORT).show();

    }

Answer 1

Firstly, FROM is a reserved word in SQLite. Don't use it as a column name.

If you only care about "from 5am to 7am", for example, then store two INTEGER fields for "seconds since start of day". Or minutes/hours, depending on the granularity of the data you have.

If you need something more complex, (say, your times overlapped at midnight, and the from was one day, but the to was the next day), then, obviously, you'll have to store the data somewhat differently.

However you want to implement that is up to you, but start with

final String TABLE_NAME = "proname";
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" 
 +  "ImageID INTEGER, " 
 +  "Title TEXT, " 
 +  "FromTime INTEGER, "
 +  "ToTime INTEGER" // I think you missed this field
 +  ");");

As for the remainder of the code - I don't really understand loading the adapter, and then loading the database. You could just load the database, and use a CursorAdapter against the ListView.

Along with that point, "have you tried the insert() method instead of execSQL?"