sqlite compare timestamp with DateTime in C#

Question

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.

In what format you get the date when you select CPUH_DATE? Could you add it to the question description? — SoftwareFactor
– SoftwareFactor, Commented May 19, 2014 at 19:18

SoftwareFactor · Accepted Answer · 2014-05-19 19:37:19Z

4

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');

answered May 19, 2014 at 19:37

SoftwareFactor

8,6283 gold badges32 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Russon Over a year ago

Just in case someone out there is looking to convert a DateTime generated by .Net code (stored in SQLite as bigint): select datetime(lastModified/10000000 - 62135596800, 'unixepoch') from YourTable More here: link

user3682728 Over a year ago

For the Developer using c# , please see Ryan Russon 's answer. As for my case I'm using SQLite-net in Xamarin project , this library has build-in method to create table in sqllite DB ( my auto generated table has DateTime field.). When query the statement "Date(mycolumnDatetime)" will got null value !

Collectives™ on Stack Overflow

sqlite compare timestamp with DateTime in C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related