none
dates in sqlite

    Question

  • Hallo,

    This is my first time working with databases.

    I have been trying for days to set up a sqlite database wich must have a column for storing dates. I have try storing the date as TEXT, and as REAL. SToring it as Text gives me a lot of trouble when trying to retrieve the data sorted. It's just never correct. I tried storing dates as REAL but with what i did i couldnt convert the dates correctly either.

    I would like to store the dates independent from the current culture. Who can help me with a real good example or a good explanation. I constantly read that i have to use the buildin convertfuctions of sqlite but no one shows an example. 

    This is my code until now:

    public partial class MainWindow : Window { public MainWindow() { InitializeComponent(); } // Holds our connection with the database SQLiteConnection m_dbConnection; string dbName = "MyDatabase.sqlite"; string tableName = "myTable"; string tableColumns, tableColumnsWthType, tableColumnsForValues; // Creates an empty database file void createNewDatabase() { SQLiteConnection.CreateFile(dbName); } // Creates a connection with our database file. void connectToDatabase() { m_dbConnection = new SQLiteConnection("Data Source=" + dbName + ";Version=3;"); m_dbConnection.Open(); } // Creates a table

    void createTable() { tableColumns = "Date, score, score2"; tableColumnsForValues = "@Date, @score, @score2"; tableColumnsWthType = "Date REAL primary key, score int, score2 int"; string sql = "create table " + tableName + " (" + tableColumnsWthType + ")"; SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection); command.ExecuteNonQuery(); } void fillTable() { SQLiteCommand command = new SQLiteCommand(m_dbConnection); DateTime hh = DateTime.Now; using (command) { using (var transaction = m_dbConnection.BeginTransaction()) { command.Parameters.AddWithValue("@Date", ""); command.Parameters.AddWithValue("@score", ""); command.Parameters.AddWithValue("@score2", ""); command.CommandText = "INSERT INTO " + tableName + " (" + tableColumns + ") VALUES (" + tableColumnsForValues + ");"; // 100,000 inserts for (var i = 0; i < 100000; i++) { hh= hh.AddSeconds(1); //var dateString = hh.ToString(); //DateTime dt = DateTime.ParseExact(dateString, "M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture); //string txtFedCat = hh.ToString("yyyy-MM-dd HH:mm:ss"); //select datetime( 1323648000, 'unixepoch' ); TimeSpan span = hh.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)); double u= span.TotalSeconds; command.Parameters["@Date"].Value = u; command.Parameters["@score"].Value = i; command.Parameters["@score2"].Value = 1000000 - i; command.ExecuteNonQuery(); } transaction.Commit(); } } // m_dbConnection.Close(); } public DataTable GetDataTableFast(string query) { //reopen connectio if connection is closed if (m_dbConnection.State != ConnectionState.Open) m_dbConnection.Open(); IDataReader rdr = new SQLiteCommand(query, m_dbConnection).ExecuteReader(); DataTable resultTable = GetDataTableFromDataReader(rdr); m_dbConnection.Close(); return resultTable; } private DataTable GetDataTableFromDataReader(IDataReader dataReader) { DataTable schemaTable = dataReader.GetSchemaTable(); DataTable resultTable = new DataTable(); foreach (DataRow dataRow in schemaTable.Rows) { DataColumn dataColumn = new DataColumn(); dataColumn.ColumnName = dataRow["ColumnName"].ToString(); dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString()); dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"]; dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"]; dataColumn.Unique = (bool)dataRow["IsUnique"]; resultTable.Columns.Add(dataColumn); } string r=""; while (dataReader.Read()) { DataRow dataRow = resultTable.NewRow(); for (int i = 0; i <= resultTable.Columns.Count - 1; i++) { dataRow[i] = dataReader[i]; r = r + "_" + dataRow[i].ToString(); } this.listBox1.Items.Add(r); r = ""; resultTable.Rows.Add(dataRow); } return resultTable; } private void button4_Click(object sender, RoutedEventArgs e) { fillTable(); } private void button5_Click(object sender, RoutedEventArgs e) { string query = "SELECT * FROM " + tableName + " WHERE score<=90010 And score>=50000 ORDER BY Date"; GetDataTableFast(query); } }



    Jc

    Tuesday, November 5, 2013 8:06 AM

Answers

All replies

    • Marked as answer by bochelie Tuesday, November 5, 2013 11:08 AM
    Tuesday, November 5, 2013 8:58 AM
  • Ok. Thanks. Thats very clear. Just one more question. I would like to have a good performance. So is there an easy way of using REAL to store the datetime? I heard that you must implement your own conversions and then you would have to take care of leap years and ect. Is there an easy way? I need to retrieve a lot of sorted records. And i guess it would be faster if the datetimes are just in the REAL format.

    Jc

    Tuesday, November 5, 2013 10:16 AM
  • Never tested that. But i would guess that using INTEGER with Unix Time is the fastest.

    Tuesday, November 5, 2013 12:07 PM