CODEWITHSUNDEEP
c#sqlitedatetime
ren
ren

Reputation: 3993

sqlite compare timestamp with DateTime in C#

How do I compare sqlite timestamp with C# DateTime? I have:

string query = string.Format(@"select avg(CPUH_VALUE_ALL) as Value from CPU_HOUR where CPUH_DATE >= @Date");
var pars = new List<SQLiteParameter>();
pars.Add(new SQLiteParameter("Date", DateTimeSQLite(DateTime.Now.AddMinutes(-12))));

where 

static string DateTimeSQLite(DateTime datetime)
{
    string dateTimeFormat = "{0}-{1}-{2} {3}:{4}:{5}.{6}";
    return string.Format(dateTimeFormat, datetime.Year, datetime.Month, datetime.Day, datetime.Hour, datetime.Minute, datetime.Second, datetime.Millisecond);
}

That wouldn't work. And nothing works when I try compare timestamp with string value of date in various formats either. Any clues?

Edit

select CPUH_DATE from CPU_HOUR where CPUH_DATE

get me records like this:

5/19/2014 9:30:54 PM

Edit2 I've just discovered that while this is not working:

select * from CPU_HOUR where CPUH_DATE >= '2014-5-19 21:30:08'

this is (note the zero before 5):

select * from CPU_HOUR where CPUH_DATE >= '2014-05-19 21:30:08'

Interesting.

Upvotes: 3

Views: 2700

Answers (1)

SoftwareFactor
SoftwareFactor

Reputation: 8588

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Source: sqllite.org

My suggestion would be to just save the datetime values as integer. This way you will have no problems with operations such as comparision. And if you ever need non-numeric date in your query, you can use built in Datetime function like this:

SELECT datetime(1092941466, 'unixepoch');

Upvotes: 4

Related Questions