How to insert time stamp into an SQLite database column? Using the function time('now')?

Question

I am working on an android app and I am creating a database called HealthDev.db that has a table called rawData that has 4 columns: _id, foreignUserId, data, timeStamp

I have worked with the program sqlite3 in the bash shell and have figured out that I can have a time stamp column with the following column schema parameter: timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP

so when I created the table I used: create table rawData(_id integer primary key autoincrement, foreignUserId integer, data real, timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

This worked fine in the bash.

Then I practiced in the sqlite3 and know that when inserting into the timeStamp column and using the function time('now') as a value to store it actually stores a time stamp in the form HH:MM:SS in Universal Coordinated Time.

So now translating that into java for the android app, I used the following code below. This way the table automatically generates about 20 rows when the onCreate is called. This is just for testing if I am passing the time('now') correctly in java.

        // Below are variables to the database table name and the 
// database column names.
public static final String TABLE_RAW_DATA = "rawData";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_FOREIGN_USER_ID = "foreignUserId";
public static final String COLUMN_DATA = "data";
    public static final String COLUMN_TIME_STAMP = "timeStamp";

// Database creation sql statement.
private static final String DATABASE_CREATE = "create table "
    + TABLE_RAW_DATA 
    + "(" 
    + COLUMN_ID + " integer primary key autoincrement, " 
    + COLUMN_FOREIGN_USER_ID + " integer, " 
    + COLUMN_DATA + " real, " 
    + COLUMN_TIME_STAMP + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
    + ");";

// initializes the columns of the database given by passing the DATABASE_CREATE
// sql statement to the incoming database.
public static void onCreate(SQLiteDatabase database) {
    database.execSQL(DATABASE_CREATE);
    // For testing

    ContentValues contentValues = new ContentValues();
    System.out.println("The database is open? " + database.isOpen());
    for (int i = 0; i < 20; i++)
    {
        contentValues.put( COLUMN_FOREIGN_USER_ID, 8976);
        contentValues.put( COLUMN_DATA, Math.random()*100 );
        contentValues.put( COLUMN_TIME_STAMP, " time('now') " );

        database.insert( TABLE_RAW_DATA, null, contentValues );

        //contentValues = new ContentValues();

    }

  }

After running this code in an eclipse emulator I then pulled the database file from the file explorer in DDMS view mode for eclipse android projects. Then I opened the database in a bash shell and then selected all the columns from the table rawData to show it on the shell. I noticed that the time('now') was treated as a string and not a function. To prove that the time('now') function worked I manually inserted a new row using time('now') for the timeStamp value. Then re selected all the columns to show them again. It successfully printed the time stampe as HH:MM:SS.

I am thinking there might be a difference in the enviroments? The bash shell recognizes the function time('now'), which was written in c right?, because I have the sqlite3 program in the bash? Yet in eclipse when I use a SQL database and use the insert it treats the time('now') as a string. Keep in mind I am working in a Windows 7 os. I am accessing the bash as a client (SSH Secure Shell) from my school which is the host.

My main question is it possible to code it so that way it recognizes the time('now') function?

Answer 1

Since the default for the column is CURRENT_TIMESTAMP, what if you leave out entirely this line:

contentValues.put( COLUMN_TIME_STAMP, " time('now') " );

Won't it now insert the current timestamp into that column by default?