如何将日期时间数据导入到sqlite3？

Question

我有一个 CSV 文件，其中包含以下格式的时间戳：

 Datetime, Outdoor, Indoor
 01/01  01:00:00,24.5,21.3
 01/01  02:00:00,24.3,21.1
 01/01  03:00:00,24.1,21.1
 01/01  04:00:00,24.1,20.9
 01/01  05:00:00,25.,21.
 01/01  06:00:00,26.,21.
 01/01  07:00:00,26.6,20.3
 01/01  08:00:00,28.,21.
 01/01  09:00:00,28.9,21.5
 01/01  10:00:00,29.4,22.1
 01/01  11:00:00,30.,22.
 01/01  12:00:00,29.,23.
 01/01  13:00:00,28.4,22.9
 01/01  14:00:00,27.8,22.7
 01/01  15:00:00,27.3,22.3
 01/01  16:00:00,27.,22.
 01/01  17:00:00,26.,21.
 01/01  18:00:00,26.,21.
 01/01  19:00:00,26.3,21.4
 01/01  20:00:00,26.,21.
 01/01  21:00:00,25.9,21.1
 01/01  22:00:00,25.8,21.3
 01/01  23:00:00,25.6,21.4
 01/01  24:00:00,25.5,21.5

即日期时间格式为“%m/%d %H:%M:%S”。如何将其导入到 sqlite3 中？

Answer 1

“01/01”不是日期。如果您的意思是“21.0”，请输出“21.0”，而不是“21.”。

编辑您的 csv 文件（或者更好的是，修复输出该垃圾的程序），使其看起来像这样。

2011-01-01  01:00:00,24.5,21.3
2011-01-01  02:00:00,24.3,21.1
2011-01-01  03:00:00,24.1,21.1
2011-01-01  04:00:00,24.1,20.9
2011-01-01  05:00:00,25.0,21.0
2011-01-01  06:00:00,26.0,21.0
2011-01-01  07:00:00,26.6,20.3
2011-01-01  08:00:00,28.0,21.0
2011-01-01  09:00:00,28.9,21.5
2011-01-01  10:00:00,29.4,22.1
2011-01-01  11:00:00,30.0,22.0
2011-01-01  12:00:00,29.0,23.0
2011-01-01  13:00:00,28.4,22.9
2011-01-01  14:00:00,27.8,22.7
2011-01-01  15:00:00,27.3,22.3
2011-01-01  16:00:00,27.0,22.0
2011-01-01  17:00:00,26.0,21.0
2011-01-01  18:00:00,26.0,21.0
2011-01-01  19:00:00,26.3,21.4
2011-01-01  20:00:00,26.0,21.0
2011-01-01  21:00:00,25.9,21.1
2011-01-01  22:00:00,25.8,21.3
2011-01-01  23:00:00,25.6,21.4
2011-01-01  24:00:00,25.5,21.5

然后设置分隔符并导入文件。

sqlite> .separator ','
sqlite> .import filename tablename