CODEWITHSUNDEEP
sqldatetimesqlite
ThN
ThN

Reputation: 3276

Why would SQL statement return out of ordered dataset?

I have written a program that logs events into a db file as they happen using SQL statement. Whenever I open the table to view, I specifically request the dataset in descending order based on ALARM's date and time. It seems to work only for part of the table. I am using SQLite3 and my program is written in delphi or Pascal.

Here is the SQL statement:

SELECT * 
FROM Alarms 
ORDER BY datetime(ALARMTIME) DESC

Here is a snapshot of the table. Pay attention to the red arrows. That's where Alarm's date and time doesn't follow descending order. I don't know why this is happening.

enter image description here

Upvotes: 0

Views: 199

Answers (1)

lurker
lurker

Reputation: 58294

I'm not sure how you created your date/time string in your database since that information wasn't given in the question. However, according to the documentation for the datetime() function, the format you have of MM/DD/YYYY HH:MM:SS xx is not one of the accepted formats. In fact, if you do SELECT datetime('1/23/2018 01:40:00 PM') at an SQLite prompt, you get NULL. Whereas, if you use an acceptable format, you do not: SELECT datetime('2018-01-23') gives '2018-01-23 00:00:00'.

So I think the solution is to write the ALARMTIME field using datetime('now'), which does yield a format accepted by datetime(). If you need to sort based upon the format you have now, you can't do it with datetime(). You'd need to reformat the field using string functions to get it in a form you could do a string compare with (e.g., as is done here).

The OP has indicated in a comment that the ALARMTIME is set using the following Pascal code:

FieldByName('AlarmTime').AsDateTime := now;

There's no guarantee that Pascal is going to use a date/time string format in this context that is compatible with SQLite's datetime() function. So Pascal date/time formatting functions can be used to create a format more specifically acceptable by SQLite's datetime(). Then you'd use something like:

FieldByName('AlarmTime').AsString := FormatDateTime('YYYY-MM-DD hh:nn:ss',now);

Now this will change the default view of the date to YYYY-MM-DD .... If you still want your view of the table to show MM/DD/YYYY... then you'll either need to go back to my prior comment about processing the string on the fly in the comparison for sort, or write a little view formatting code so that it displays in a format that's different than what is stored internally, which is a common view/model separation technique.

If you can write your original ALARMTIME format as MM/DD/YYYY and make sure you pre-pad with zeroes (e.g., 01/09/2018 not 1/9/2018) then you can use SQLite's substr function:

SELECT * FROM Alarms
    ORDER BY (substr(ALARMTIME,7,4)||substr(ALARMTIME,1,2)||substr(ALARMTIME,4,2)||substr(ALARMTIME,11)) DESC

And you would create your ALARMTIME using this:

FieldByName('AlarmTime').AsString := FormatDateTime('dd-mm-yyyy hh:nn:ss', now);

The above solutions are fairly generic. Depending upon the client library you are using (which you have not specified), there may be another more suitable approach to solving the problem.

You cannot do, for example, SELECT * FROM Alarms ORDER BY ALARMTIME DESC and get an accurate sort since, for example, the date 12/1/2018 would come after 2/1/2018 in that sort ordering even though 2/1/2018 is later in time. This is because 2 follows 1 in the ASCII collating sequence.

If you need to keep your current ALARMTIME string format and not change how you're saving it, which is somewhat free form m/d/yyyy in which the day or month can have one or two digits, you're going to have a bit of work to do in order to sort it if your client library doesn't support some helpers in this regard. Perhaps your only other option would be to use a custom SQLite function. These are written in C and compiled and linked with SQLite. You'd have to find one already written, or write your own.

Upvotes: 3

Related Questions