2 Replies Latest reply on Aug 3, 2009 10:42 AM by adobe_paul

    help for date compare in sqlite?

    micromms

      Hi all,

            How to decide a date is between two date or not? for example, I want to query the tasks in the next week from the sqlite db, how to do?

           

              SELECT  * FROM TASK_TD WHERE TASK_DATE > NOW AND TASK_DATE < NEXTWEEK_DATE


          That does not work!

         Anybody can help me?Thanks a lot.



      David

        • 1. Re: help for date compare in sqlite?
          lironl

          Hi

           

          I hate to be the one the brings you the bad news but SQLite has very poor support for datetime fields. Basically - it treats them like string fields (that's why the comparison fails).

           

          If you really want to compare dates there are two options:

           

          1. Create separate columns for the various date components and sort using these columns. Add indexes so that the search is fast.

           

          2. Add a custom SQLite function for this purpose (see more about it in http://sqlite.org/cintro.html under 'Extending SQLite' section). Note that in such case you'll suffer slow performance if you have large tables (since dates are not indexed correctly).

           

          Good Luck

          Liron Levi - creator of the SQLite Compare (http://www.sqlitecompare.com) diff/merge utility.

          • 2. Re: help for date compare in sqlite?
            adobe_paul Adobe Employee

            If you're using ActionScript (Flex or Flash) for your database, you could also declare your columns as "Date" (i.e. the ActionScript Date class). AIR has added support for Date as a recognized data type in its SQLite implementation:

             

            http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#columnAffinity

            http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#additionalSQL

             

            By using statement parameters to pass in Date data the ActionScript Date objects are converted when you insert data, and when you retrieve data it automatically comes back as ActionScript Date objects.

             

            Internally in the database, the Date values are stored as SQLite REAL (Unix epoch format) so date comparisons should work fine. For instance, if you want to find all rows with a date in a certain range, you could use this SQL:

             

            SELECT *

            FROM mytable

            WHERE datecolumn BETWEEN :startdate AND :enddate

             

            Then you'd need to specify the :startdate and :enddate values as parameters:

             

            sqlStatement.parameters[":startdate"] = new Date(2000, 3, 15);

            sqlStatement.parameters[":enddate"] = new Date(2005, 6, 13);

             

             

            2. Add a custom SQLite function for this purpose (see more about it in http://sqlite.org/cintro.html under 'Extending SQLite' section). Note that in such case you'll suffer slow performance if you have large tables (since dates are not indexed correctly).

            Unfortunately AIR doesn't support custom functions in SQLite.