Why would SQL statement return out of ordered dataset?

Question

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.

I noticed at the point where the sequence jumps suddenly the Priority and State values (and all the other values to the right of "Message" are not zero and could be some kind of outlier. I suppose that there could be a secondary key in the table interfering with this operation. — Andrew Lau, Feb 13 '18 at 20:12
Is your `ALARMTIME` originally a string of the form `MM/DD/YYYY MM:HH:SS xx`? According to the [documentation for `datetime()`](https://www.techonthenet.com/sqlite/functions/datetime.php), that's not one of the formats it accepts. — lurker, Feb 13 '18 at 20:17
The weird thing about this issue is that whenever my program enters a record, it shows up right below the red arrows. Technologically, that's where you would expect the latest entry to be, but the position of the date 1/23/18 doesn't make sense. — ThN, Feb 13 '18 at 20:24
From that sample, it seems the sorting only consider the day of the month. — Ken Bourassa, Feb 13 '18 at 20:25
@ThN did you read my prior comment? The date format you are passing to `datetime()` isn't one of the accepted formats. Go to the [online SQLite browser](https://sqliteonline.com/) and try some sample queries. `select datetime('2018-01-23');` yields `'2018-01-23 00:00:00'`. But `select datetime('1/23/2018 01:40:00 AM');` yields NULL. You'll need to string process your date string a bit and use that for comparison (*e.g.*, as is done [here](https://stackoverflow.com/questions/4428795/sqlite-convert-string-to-date)). — lurker, Feb 13 '18 at 20:26
well, i don't know what format datetime the procedure `now` returns. That's how my program sets the column whenever it enters a new record. For display purpose, I format the date and time as it should look for the alarm viewer. — ThN, Feb 13 '18 at 20:32
`datetime()` accepts the string `'now'` as an input and it interprets that as... "now". `now` is not a procedure in that context. If you look at the documentation for `datetime()` it tells you the format it expects. The format of your date/time string doesn't match any of them. Perhaps when you create a record you could write the current date/time in a format it accepts. If you wrote the current date using `datetime('now')` it would be an accepted format. — lurker, Feb 13 '18 at 20:36

lurker · Accepted Answer · 2018-02-15T15:38:11.107

3

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.

edited Feb 15 '18 at 15:38

answered Feb 13 '18 at 20:49

lurker

56,987
9
69
103

`FieldByName('AlarmTime').AsDateTime := now;` – ThN Feb 13 '18 at 20:51
@ThN That `now` function is not an SQLite function. It's a Pascal function (Free Pascal or Borland Pascal?). There's no guarantee that Pascal is going to, by default, format date/time the way SQLite `datetime()` wants to see it. You could look around to see what Pascal date/time formatting options you have available to get it into the format that SQLite likes for `datetime()`. – lurker Feb 13 '18 at 21:27
@ThN what is the actual type of the `ALARMTIME` field? Is it a string? – lurker Feb 13 '18 at 21:37
@lurker ALARMTIME is TDATETIME not a string. – ThN Feb 13 '18 at 21:47
@lurker if I set the Alarmtime as String and format the string as you have it in your answer, then it will also appear in the same way in the table when you view it. Right? It won't be Month-Day-Year. – ThN Feb 13 '18 at 21:50
@ThN I'm afraid that is true. If you don't want that, then you're back to my earlier suggestion where you need to do your own custom processing of the string in your `SELECT` to be able to compare two dates of the form `MM/DD/YY` and get the order to come out right. See the link I put in my answer for an example of that type of processing. – lurker Feb 13 '18 at 21:55
@ThN the other option is to have the data in your table internally be `YYYY/MM/DD ...` but write some code that displays it as `MM/DD/YYYY ...` in your view. That is actually a common way of handling data: separating the internal model requirements from the view requirements. – lurker Feb 13 '18 at 21:58
@ThN I believe I've answered the original question as well as offered a couple of different approaches to work around it. Is my answer acceptable? – lurker Feb 14 '18 at 12:37
@lurker Yes and you have cleared a lot of my confusion as well. Finally, I got the sort to work but it is only sorting in ascending order not in descending order. `SELECT * FROM Alarms ORDER BY strftime(ALARMTIME, 'yyyy-mm-dd') DESC;` If you can quickly respond to this comment, I will select your answer. Thanks. – ThN Feb 14 '18 at 15:33
@ThN, unfortunately `strftime(ALARMTIME, 'yyyy-mm-dd')` doesn't match the argument requirements for `strftime` if you look at the SQLite documention (NOTE: it's the SQLite function, not the Pascal function). The first argument is supposed to be a date format string. The second is the actual date but in the required format that `datetime()` needs. So I'm afraid that function is not going to work for you. All of your `strftime` calls are probably returning NULL and so you're getting a default ordering of records. I recommend one of my other suggestions already stated in my answer. – lurker Feb 14 '18 at 15:58
@ThN I've updated my answer again to include a possible SQL string that may help, with some conditions on format of your ALARMTIME. – lurker Feb 14 '18 at 16:27
@lurker unfortunately nothing is working for me. I tried `SELECT *, strftime('%Y/%m/%d', ALARMTIME) as ATIME FROM Alarms ORDER BY (substr(ATIME,7,4)||substr(ATIME,1,2)||substr(ATIME,4,2)) DESC` also `SELECT * FROM Alarms ORDER BY DateTime(strftime('%s',ALARMTIME), 'unixepoch')) DESC` This is strange indeed and I think I have to change ALARMTIME to string column. That's probably the ONLY WAY. That means rewriting a lot of my code...I tested these Queries on the online SQLite browser and it worked. However, I am not getting any error messages when I execute these Queries. – ThN Feb 14 '18 at 20:52
@ThN as I've mentioned a couple of times already, the SQLite date functions `strftime` and `datetime` do not accept your `ALARMTIME` format as a valid format. So `strftime('%Y/%m/%d', ALARMTIME)` is going to yield NULL. For this to work, you need to do as I mentioned in my answer, which is to save the `ALARMTIME` out originally as the zero-padded date that I described. Is there a reason you cannot do that? In other words, `FieldByName('AlarmTime').AsString := FormatDateTime('mm/dd/yyyy hh:nn:ss', now);`would be required. That would give you `01/23/2018` instead of `1/23/2018`. – lurker Feb 14 '18 at 21:40
@lurker Yes, I understand that I will have to turn AlarmTime into string type to be able to sort my dataset as you explained. That would require me to modify a lot of my code which I am not ready to do. However, just to try it out and see what happens I simply removed `datetime` core function from the SQL statement like so `SELECT * FROM Alarms ORDER BY ALARTIME DESC` and it works. Next time I have to remember to keep date and time columns as string. – ThN Feb 15 '18 at 14:54
What lurker wrote here doesn't need to be true e.g. for FireDAC client library. – Victoria Feb 15 '18 at 14:58
@Victoria the OP hasn't even mentioned the FireDAC client library. – lurker Feb 15 '18 at 15:00
@ThN ordering by just `ALARMTIME` will not always work since, for example, `10/1/2018` will come after `2/1/2018` in descending sort order. – lurker Feb 15 '18 at 15:01
That's why I've asked which client library they use. You wrote this as a generic answer, but for FireDAC, e.g. you'd do something else. – Victoria Feb 15 '18 at 15:01
@Victoria yes, it would be nice to know! To me, it looks like the Lazarus (or Delphi) database accessing. In the meantime, I could only provide a generic answer and provide responses to the OP's immediate issues. If the OP comes back later (unlikely) and says they are indeed using FireDAC (which is only one of any options), then of course the answer may change. That sort of thing could be said of many questions and answers here on SO. If you think it's likely they're using FireDAC, or want to benefit those who are, you could post an answer assuming FireDAC. – lurker Feb 15 '18 at 15:02
1

@Victoria, I added a comment to my answer to qualify it further. – lurker Feb 15 '18 at 15:14
@luker @Victoria... I am not sure what do you mean by Client Library in this case. I am using SQLite3 components to read and write into the db file. As far as `10/1/2018` being out of order, I just did a test on this date and sure enough it is showing up where it should be at the TOP of the table. ALARMTIME is of the type ftDateTime if that means anything to you. – ThN Feb 15 '18 at 16:04
@ThN client library would be the functional API you're using in Pascal to interface to SQLite3. Delphi and Lazarus provide their own, but there are also other 3rd party libraries. SQLite3 does not natively have a date/time type. So although it's `ftDateTime` to Pascal, it's a string to SQLite3. Thus, the SQLite `SELECT` query you use ordering on `ALARMTIME` may not work. However, in Pascal, if you are populating a Pascal table structure/view, it may have a way of ordering on `ftDateTime` which may help you. – lurker Feb 15 '18 at 16:58
@ThN You haven't said what programming environment you're using (I know it's Pascal, but is it Delphi or Lazarus for example), nor what structure/class you're using, so there may be other options for solving your problem if those are known. It may be easier to provide the sorting at that level rather than the SQL level. – lurker Feb 15 '18 at 17:00
@lurker I know you've been saying it may not work, but so far everything that you've been throwing at me to test is working as it should. I just tested the date `12/1/2018` and it doesn't come after `2/1/2018` as you say. I am programming using Lazarus IDE on Linux Operating System. – ThN Feb 15 '18 at 18:50
@ThN, ok I was taking some guesses as to what the date/time looks like to SQLite, not knowing any more about your underlying data schema, thinking it would be a string format. I know that in string format, `12/1/2018` comes before `2/1/2018` in the ASCII collating sequence, which in a DESC context would cause `12/1/2018` to come after `2/1/2018`. If that's not happening then it must not be treated as a string and that's good news for you. :) – lurker Feb 15 '18 at 19:09

Why would SQL statement return out of ordered dataset?

1 Answers1