SQLite - Ordering

Question

I have a strange issue where upon selecting information from a SQLite database, ans ordering based upon date, the results returned are invalid.

My SQL statement is as such:

Select pk from usersDates order by datetime(usersDate, 'localtime') ASC

I have dates stored in the database which range as far as 2111. However the order the data is returned in indicates that dates from 2036 happen after the ones from 2111. The column 'usersDate is actually a double (time interval since 1970 / unix time) - hence the reason for the cast.

Does anyone know what would cause this?

Answer 1

You should re-read the date and time syntax. The 'localtime' modifier expects an UTC time on its left.

Use SELECT pk FROM usersDates ORDER BY datetime(usersDate, 'unixepoch', 'localtime') ASC.