java 如何在 SQLite 数据库列中插入时间戳？使用函数 time('now')？

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

提问by fgharo91

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

我正在开发一个 android 应用程序，我正在创建一个名为 HealthDev.db 的数据库，该数据库有一个名为 rawData 的表，该表有 4 列：_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

我在 bash shell 中使用过 sqlite3 程序，并发现我可以有一个带有以下列模式参数的时间戳列：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);

所以当我创建我使用的表时： create table rawData(_id integer primary key autoincrement, foreignUserId integer, data real, timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

This worked fine in the bash.

这在 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.

然后我在sqlite3中练习，知道当插入timeStamp列并使用函数time('now')作为值来存储它时，它实际上在通用协调时间中以HH:MM:SS的形式存储了一个时间戳。

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.

所以现在将它翻译成 android 应用程序的 java，我使用了下面的代码。这样，当调用 onCreate 时，表会自动生成大约 20 行。这仅用于测试我是否在 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.

在 Eclipse 模拟器中运行此代码后，我以 DDMS 视图模式从文件资源管理器中为 Eclipse android 项目提取数据库文件。然后我在 bash shell 中打开数据库，然后从表 rawData 中选择所有列以在 shell 上显示它。我注意到 time('now') 被视为字符串而不是函数。为了证明 time('now') 函数有效，我使用 time('now') 作为 timeStamp 值手动插入了一个新行。然后重新选择所有列以再次显示它们。它成功地将时间戳打印为 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.

我在想环境可能会有所不同？bash shell 可以识别函数 time('now')，它是用 c 编写的，对吗？，因为我在 bash 中有 sqlite3 程序？然而在 Eclipse 中，当我使用 SQL 数据库并使用插入时，它将时间（'现在'）视为字符串。请记住，我在 Windows 7 操作系统中工作。我正在从作为主机的学校访问 bash 作为客户端（SSH 安全外壳）。

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

我的主要问题是是否可以对其进行编码，以便识别 time('now') 函数？

回答by mharper

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

由于该列的默认值是 CURRENT_TIMESTAMP，如果您完全省略这一行会怎样：

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

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

默认情况下，它现在不会将当前时间戳插入该列吗？