Databases
StackOverflow2013
Postsdbo
POSQLite sort order on datetime column populated from C# with DateTimeOffset
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLite sort order on datetime column populated from C# with DateTimeOffset
    text
    copied!<p>I'm working on a Windows 8 Metro application that uses SQLite as its local database. </p> <p>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. </p> <p>The datetime column is populated with data generated by the Metro application in C#; for example as <code>DateTimeOffset.Now</code>. WinRT doesn't support <code>DateTime</code>, so we're using <code>DateTimeOffset</code>.</p> <p>You can replicate this in a simple table:</p> <pre><code>CREATE TABLE "Foo" ("DateOfFoo" DATETIME) </code></pre> <p>Here's some sample data based on data from our application (note that I'm in the PDT timezone which is UTC -7):</p> <pre><code>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 </code></pre> <p>A simple query such as:</p> <pre><code>Select * From Foo Order By DateOfFoo </code></pre> <p>returns the following resultset:</p> <pre><code>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 </code></pre> <p>when I would expect:</p> <pre><code>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 </code></pre> <p>and <code>Select * From Foo Order By DateOfFoo DESC</code> returns:</p> <pre><code>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 </code></pre> <p>when I would expect:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload