How to add datetime function in SQLite

Question

Possible Duplicate:
How to insert a SQLite record with a datetime set to ‘now’ in Android application?

i'm developing an app which stores name, password etc to the database. And i also want to store current date and time to the database and retrieve back later. I'm unable to crack how to store date and time in SQLite database. Should i use datetime function?? If yes how to add it in my proceeding example. Please help.

Thanks in advance.

public class DBAdapter{public static final String ROWID = "_id";
public static final String NAME = "name";
public static final String EMAIL = "email";
public static final String PASSWORD = "password";
public static final String TYPE = "type";
private static final String TAG = "DBAdapter";
private static final String DATABASE_NAME = "primus_db";
private static final String DATABASE_TABLE = "user_db";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE = "create table user_db (_id integer primary key autoincrement, "
        + "name text not null, "
        + "email text not null, "
        + "password text not null," + "type text not null );";
private final Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase db;

/**
 * 
 */
public DBAdapter(Context ctx) {
    this.context = ctx;
    this.DBHelper = new DBAdapter.DatabaseHelper(this.context);
}

private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            db.execSQL(DATABASE_CREATE);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }
}

// ---opens the database---
public DBAdapter open() throws SQLException {
    db = DBHelper.getWritableDatabase();
    return this;
}

// ---closes the database---
public void close() {
    DBHelper.close();
}

// ---insert a contact into the database---
public long insertContact(String name, String email, String password,
        String type) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(NAME, name);
    initialValues.put(EMAIL, email);
    initialValues.put(PASSWORD, password);
    initialValues.put(TYPE, type);
    return db.insert(DATABASE_TABLE, null, initialValues);
}

// ---retrieves all the contacts---
public Cursor getAllContacts() {
    return db.query(DATABASE_TABLE, new String[] { ROWID, NAME, EMAIL,
            PASSWORD, TYPE }, null, null, null, null, null);
}

}

score 2 · Accepted Answer · answered Dec 30 '12 at 06:26

You can follow different ways. You can use DATETIME('NOW') while inserting timestamps into the records, can't you? Or you can use this while creating table.

time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

where time is the column which takes current time.

For more details refer this

score 0 · Answer 2 · answered Dec 30 '12 at 06:38

If you have plan to reuse that date/time data like "between start date and end date" function or something like that, I would recommend using timestamp like DATETIME('NOW') and use long datatype. Or if it is just for record, you can use currentDateTime.getTime()) and use text datatype.

How to add datetime function in SQLite

2 Answers2