Search code examples
sqlitemicrosoft-metro

SQLite sort order on datetime column populated from C# with DateTimeOffset

I'm working on a Windows 8 Metro application that uses SQLite as its local database.

One of the tables in the database contains a datetime column. I'm seeing unexpected results when running queries that sort the resultset by the datetime column - the resultset isn't ordered how you would expect.

The datetime column is populated with data generated by the Metro application in C#; for example as DateTimeOffset.Now. WinRT doesn't support DateTime, so we're using DateTimeOffset.

You can replicate this in a simple table:

CREATE TABLE "Foo" ("DateOfFoo" DATETIME)

Here's some sample data based on data from our application (note that I'm in the PDT timezone which is UTC -7):

INSERT INTO "main"."Foo" ("DateOfFoo") VALUES (?1)
Parameters:
param 1 (text): 9/18/2012 8:08:56 AM -07:00

INSERT INTO "main"."Foo" ("DateOfFoo") VALUES (?1)
Parameters:
param 1 (text): 9/18/2012 8:13:42 AM -07:00

INSERT INTO "main"."Foo" ("DateOfFoo") VALUES (?1)
Parameters:
param 1 (text): 9/18/2012 12:46:36 PM -07:00

A simple query such as:

Select * From Foo Order By DateOfFoo

returns the following resultset:

9/18/2012 12:46:36 PM -07:00
9/18/2012 8:08:56 AM -07:00
9/18/2012 8:13:42 AM -07:00

when I would expect:

9/18/2012 8:08:56 AM -07:00
9/18/2012 8:13:42 AM -07:00
9/18/2012 12:46:36 PM -07:00

and Select * From Foo Order By DateOfFoo DESC returns:

9/18/2012 8:13:42 AM -07:00
9/18/2012 8:08:56 AM -07:00
9/18/2012 12:46:36 PM -07:00

when I would expect:

9/18/2012 12:46:36 PM -07:00
9/18/2012 8:13:42 AM -07:00
9/18/2012 8:08:56 AM -07:00

My colleague in the CDT timezone couldn't replicate the issue. I changed the time zone on my machine to CDT and populated the application data, and sure enough the query sorts the resultset correctly:

9/18/2012 2:46:36 PM -05:00
9/18/2012 10:13:42 AM -05:00
9/18/2012 10:08:56 AM -05:00
Solution

  • SQLite doesn't support an actual date/time datatype. The dates and times are being inserted into the database as strings, and are therefore being sorted alphabetically on the SELECT statement. SQLite does support some date and time functions, but requires the strings to be in one of a list of formats, which the ones you are using do not match. For the proper formats, see the SQLite documentation.