C
C#3y ago
Nemesis

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
Unknown User3y ago
Message Not Public
Sign In & Join Server To View
pip
pip3y 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"])
Want results from more Discord servers?
Add your server