1

I am querying a SQLite database table as follows:

SELECT MAX(Date) from Intra360 WHERE Date <= "05/04/2013 00:00"

The right record in return should be the number 47, i.e. 04/04/2013 23:00:

raw data

However, the execution of this statement returns a different value:

SQL output

I confess I know almost nothing about SQL, but this outcome is strange. Where am I being wrong?

NOTE "Intra360" is the name of the table and the field containing the dates is called "Date"

ADDITIONAL NOTE what I need is the closest available date to a user input. It is a Python program which is making some analysis but when the user inputs the dates is not necessarily true they will exist in the database. So I'm just trying to re-select them in a way that the proper SQL statement that will load the data to be used in the analysis won't fail execution because of the missing record. So "05/04/2013 00:00" is the user input, and the query should be done hence starting from 04/04/2013 (and not definetely 04/06/2013).

Community
  • 1
  • 1
Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89

1 Answers1

1

The comparisons are performed on strings with alphabetical ordering, not on datetime stamps with chronological ordering.

Store your datetimes in a format that compares the way you want. For example, unix epoch timestamps and ISO 8601 yyyy-MM-dd'T'HH:mm:ss datetimes have this property.

If you cannot influence how the data is stored, you can use substr() to mangle the timestamps in SQL. See e.g. Sqlite convert string to date for more.

Community
  • 1
  • 1
laalto
  • 150,114
  • 66
  • 286
  • 303
  • I was fearing something like this. The problem is that I cannot touch the database, the values will be always stored like that. Is there a way to move around the problem from the SQL statement and not from the data structure? (sorry once again, I understand nothing of SQL so most probably my question is very stupid). – Matteo NNZ Mar 07 '14 at 12:04
  • Ok, I realized the problem. All my dates are stored as strings cause they come from external data providers that for some reason don't allow changes to this structure. I guess I will have to make a work around in Python and querying the strings. Thanks for explanation, if you know a way to do what I asked (even if I think is impossible, isn't it?) I would be glad – Matteo NNZ Mar 07 '14 at 12:11
  • Sorry, I've read your edit later. That was the solution, thanks a lot! – Matteo NNZ Mar 07 '14 at 12:12