先の記事の例がSQLiteが直接 DATETIMEを記録する型を持たずTEXTやNUMERIC(数値型でパースを試みてダメならTEXTで記録する型)に直すということについて考察が抜けている（あえてINTEGERに直していた）
どうも以下のようにしたらうまく保存できるようです．
SELECTの時にdatetime()に戻す方法もあるが，DateTimeの桁がおちてしまう（秒未満）．
さらに細かくいうと，yyyy-MM-dd HH:mm:ss.fffは本当はfffではDateTimeの桁を落としてしまうのでfffffffらしい（.NETのDateTimeは100 ns単位）．（JavaScriptなら1 ms単位）
書き込み
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
namespace SQLite2
{
public partial class Form1 : Form
{
private System.Data.SQLite.SQLiteConnection cn;
public Form1()
{
InitializeComponent();
SQLiteConnectionStringBuilder sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = "test.db" };
this.cn = new SQLiteConnection(sqlConnectionSb.ToString());
this.cn.Open();
var cmd = new SQLiteCommand(cn);
cmd.CommandText = "CREATE TABLE IF NOT EXISTS test(" +
"time DATETIME NOT NULL PRIMARY KEY," +
"value REAL)";
cmd.ExecuteNonQuery();
}
private void button1_Click(object sender, EventArgs e)
{
DateTime datetime_ = DateTime.Now;
Double value = datetime_.Ticks;
string datetime = datetime_.ToString("yyyy-MM-dd HH:mm:ss.fff");
var cmd = new SQLiteCommand(cn);
cmd.CommandText = "INSERT INTO test(time, value) "
+ "VALUES("
+ $"'{datetime}', {value})";
cmd.ExecuteNonQuery();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
cn.Close();
}
}
}
読み込み
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
namespace SQLView2
{
public partial class Form1 : Form
{
private System.Data.SQLite.SQLiteConnection cn;
private DataSet ds;
private System.Data.SQLite.SQLiteDataAdapter da;
public Form1()
{
InitializeComponent();
SQLiteConnectionStringBuilder sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = "../../../SQLite2/bin/Debug/test.db" };
this.cn = new SQLiteConnection(sqlConnectionSb.ToString());
this.cn.Open();
ds = new DataSet();
da = new SQLiteDataAdapter();
var cmd = new SQLiteCommand(cn);
cmd.CommandText = "SELECT * FROM test ORDER BY time asc";
da.SelectCommand = cmd;
da.Fill(ds, "test");
this.dataGridView1.DataSource = ds.Tables["test"];
chart1.Series[0].XValueMember = "time";// チャートへのバインド
chart1.Series[0].YValueMembers = "value";
this.chart1.DataSource = ds.Tables["test"];
this.chart1.DataBind();
}
private void timer1_Tick(object sender, EventArgs e)
{
if (ds.Tables["test"].Rows.Count > 0)
{
DateTime last = (DateTime)ds.Tables["test"].Rows[ds.Tables["test"].Rows.Count - 1][0];
var cmd = new SQLiteCommand(cn);
cmd.CommandText = "SELECT * FROM test WHERE time > "
+ $"'{last.ToString("yyyy-MM-dd HH:mm:ss.fff")}'"
+ " ORDER BY time asc";
da.SelectCommand = cmd;
}
this.da.Fill(ds, "test");
this.chart1.DataBind();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
this.cn.Close();
}
}
}
