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?

Solution

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?