database SQLite 创建一个带有时间戳列的数据库并在其中添加一个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16596126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQlite creating a database with a timestamp column and adding a value in
提问by wyatt
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.
我正在尝试创建一个名为 rawData 的数据库。db 将为 id、外部用户 id（来自另一个表的 _id）、数据和最后一个时间戳提供一列。
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?
我的问题是如何在 SQlite 中创建时间戳并将其存储在数据库中，列应该是什么类型，文本？数据库需要能够每秒存储 150 个浮点值，并为这 150 个条目中的每一个添加时间戳。另外，由于 SQlite 没有浮点类型，我应该使用 real 作为列类型吗？
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);";
}
回答by CL.
The documentationsays:
该文件说：
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functionsof SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
- TEXTas ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
- REALas Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGERas 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.
SQLite 没有专门用于存储日期和/或时间的存储类。相反，SQLite的内置日期和时间函数能够将日期和时间存储为 TEXT、REAL 或 INTEGER 值：
- TEXT作为 ISO8601 字符串（“YYYY-MM-DD HH:MM:SS.SSS”）。
- REAL作为儒略日数，根据预测公历，自公元前 4714 年 11 月 24 日中午以来格林威治的天数。
- INTEGER作为 Unix 时间，自 1970-01-01 00:00:00 UTC 以来的秒数。
应用程序可以选择以任何这些格式存储日期和时间，并使用内置的日期和时间函数在格式之间自由转换。
If you need only seconds precision, use integers in Unix Time format. Otherwise, use floating-pointer numbers for fractional seconds.
如果您只需要秒精度，请使用 Unix 时间格式的整数。否则，对小数秒使用浮点数。
回答by Alessandro Mattiuzzi
This is a running example of DAO with SQlite and Date in Java:
这是一个在 Java 中使用 SQlite 和 Date 的 DAO 运行示例：
First create column MY_DATE TIMESTAMP in your TABLE and populate like this:
首先在您的 TABLE 中创建列 MY_DATE TIMESTAMP 并像这样填充：
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));
