SQlite creating a database with a timestamp column

问题:

I am trying to create a database that called rawData. The db will hava a column for the id, a foreign user id (_id from another table), data and finally a timestamp.

My question is how can I create a timestamp in SQlite and store it in the db also what type should the column be, text? the database needs to be able to store 150 float values a second and time stamp each of those 150 entries. Additionally since SQlite doesn't have a float type should i use real as the column type?

public class RawDatabase{
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
    + " text not null);";
}

回答1:

The documentation says:

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.

If you need only seconds precision, use integers in Unix Time format. Otherwise, use floating-pointer numbers for fractional seconds.

回答2:

This is a running example of DAO with SQlite and Date in Java:

First create column MY_DATE TIMESTAMP in your TABLE and populate like this:

PreparedStatement st = connection.prepareStatement("INSERT INTO ......");
st.setDate(1, new java.sql.Date(lettura.getData().getTime()));

And to retrieve data first i get date in String type:

String dateStr = rs.getString(1);
Date myDate = new java.util.Date(Long.parseLong(dateStr));