How to select from static list of values in android sqlite, syntax error near values(

Question

I am trying to create a static list of timestamps so that i can join them agains another table to create chart data. So far I have a query in this format SELECT * FROM (VALUES('a'),('b'),('c'),('d')) AS tbl ,which is working in sqlitestudio but not in android 4.4. When I run the query in the phone I get the error

android.database.sqlite.SQLiteException: near "VALUES": syntax error (code 1): , while compiling: SELECT * ...

I have also tried wrapping the values term inside another select like this SELECT * FROM (SELECT * FROM (VALUES('a'),('b'),('c'),('d'))) AS tbl but I still get the same error.

The full query now looks like this

SELECT *  FROM (select * from ( VALUES  (1458111312025), 
                                    (1455667200000), 
                                    (1455753600000), 
                                    (1455840000000), 
                                    (1455926400000), 
                                    (1456012800000), 
                                    (1456099200000), 
                                    (1456185600000), 
                                    (1456272000000), 
                                    (1456358400000), 
                                    (1456444800000), 
                                    (1456531200000), 
                                    (1456617600000), 
                                    (1456704000000), 
                                    (1456790400000), 
                                    (1456876800000), 
                                    (1456963200000), 
                                    (1457049600000), 
                                    (1457136000000), 
                                    (1457222400000), 
                                    (1457308800000), 
                                    (1457395200000), 
                                    (1457481600000), 
                                    (1457568000000), 
                                    (1457654400000), 
                                    (1457740800000), 
                                    (1457827200000), 
                                    (1457913600000), 
                                    (1458000000000), 
                                    (1458086400000))) i  LEFT JOIN (  
                SELECT  (osysdate- (osysdate % 86400000) ) interval, SUM(field004) totalval FROM onlineactivities WHERE field003 !=300000 and osysdate> 1455605711999 GROUP BY interval ) 
                onl ON i.'' = onl.interval;;

Note that this works in sqlitestudio with sqlite version 3.8.10 but not in android kitkat (not sure about the sqlite version in it) What could be the problem?

Also please check out this question which is what I am trying to do but with sqlite and this answer

Answer 1

The VALUES syntax is available since SQLite 3.8.3, which is not available in every Android version.

To be compatible with earlier SQLite versions, you have to use a compound query:

SELECT 1458111312025 UNION ALL
SELECT 1455667200000 UNION ALL
...

Alternatively, put all the values into a temporary table.