SQlite 创建一个带有时间戳列的数据库并在其中添加一个值 [英] SQlite creating a database with a timestamp column and adding a value in

问题描述

我正在尝试创建一个名为 rawData 的数据库.db 将有一个用于 id 的列、一个外部用户 id(来自另一个表的 _id)、数据，最后是一个时间戳.

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.

我的问题是如何在 SQlite 中创建时间戳并将其存储在数据库中，该列应该是什么类型，文本?数据库需要能够每秒存储 150 个浮点值并为这 150 个条目中的每一个提供时间戳.另外，由于 SQlite 没有浮点类型，我应该使用 real 作为列类型吗?

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);";
}

推荐答案

文档说:

SQLite 没有专门用于存储日期和/或时间的存储类.相反，SQLite 的内置 日期和时间函数 能够将日期和时间存储为 TEXT、REAL 或 INTEGER价值观:

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 作为 ISO8601 字符串(YYYY-MM-DD HH:MM:SS.SSS").
• REAL 作为 Julian day numbers，自公元前 4714 年 11 月 24 日格林威治中午以来的天数.根据预测的公历.
• INTEGER 作为 Unix 时间，自 1970-01-01 00:00:00 UTC 以来的秒数.

• 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.

如果您只需要秒精度，请使用 Unix 时间格式的整数.否则，对小数秒使用浮点数.

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

这篇关于SQlite 创建一个带有时间戳列的数据库并在其中添加一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！