C#•3y ago

How to read DateTime from SQLite database using System.Data.SQLite?

I have a sqlite database with a DateTime entry. Since SQLite doesn't have a DateTime data type, it stores it as Int64 time ticks. I am able to read the correct value of the column when using any SQLite database parser available online. But when I try to read it in C#, using this code:

var cmdReadDuration = sqliteConnection.CreateCommand();
cmdReadDuration.Transaction = sqliteConnection.BeginTransaction();
cmdReadDuration.CommandText = @"SELECT * FROM Duration";
cmdReadDuration.Prepare();
var sqlReader = cmdReadDuration.ExecuteReader();
while (sqlReader.Read())
{
    var time = sqlReader["Time"];
}

var cmdReadDuration = sqliteConnection.CreateCommand();
cmdReadDuration.Transaction = sqliteConnection.BeginTransaction();
cmdReadDuration.CommandText = @"SELECT * FROM Duration";
cmdReadDuration.Prepare();
var sqlReader = cmdReadDuration.ExecuteReader();
while (sqlReader.Read())
{
    var time = sqlReader["Time"];
}

I only get 2022, the year part in the variable. What am I doing wrong?

2 Replies

Unknown User•3y ago

Message Not Public

pip•3y ago

int64 time ticks-> do you mean epoch formatted time(or unix)? also on this subject, you can store the datetime as a string in SQL and DateTime.Parse() in C#. if this database is meant to be use by many locations, make sure the date is inserted into a widely recognized format -> like ISO 8601 or something. the sqlite docs actually kind of touch on this too: https://www.sqlite.org/datatype3.html#:~:text=2.2.-,Date%20and%20Time%20Datatype,DD%20HH%3AMM%3ASS. (also, sidenote, you may need to convert the result to int64 manually = (long)sqlReader["Time"])

Gaming

Programming

How to read DateTime from SQLite database using System.Data.SQLite?