CODEWITHSUNDEEP
androidsqliteandroid-sqliteandroid-room
AndrejH
AndrejH

Reputation: 2119

SQLite - group by month based on UNIX timestamp

I have a problem using SQLite3 with Room (v2.2.5) in Android. I'm storing timestamps for some logs using UNIX timestamp in miliseconds. When I try to query them and group them by months, I get this strange error, where August and September are represented with 0 (they should be 8 and 9).

This is the query in EventLogDao:

@Query("""SELECT strftime('%m', date(timestamp / 1000, 'unixepoch')) as month, COUNT(*) as count
                FROM ${EventLog.TABLE_NAME} 
                GROUP BY month""")
abstract fun getAllSortedByTimestamp(): Single<List<TempHolder>>

Here is the output I get. If I try to count instances by months it all works normally, just the number of the month for August and September is wrong.

enter image description here

Upvotes: 2

Views: 679

Answers (1)

Shawn
Shawn

Reputation: 52579

Here's what I suspect is happening:

strftime('%m', ...) returns two-character-long strings like '01', '02', ... '08', '09', ... '12'. Your ORM is taking these strings and converting them to integers to store in this TempHolder class. Whatever conversion routine it's using looks at the leading '0' character present in 9 of the strings, decides that means those are base-8 octal constants, and converts accordingly. 08 and 09 are of course invalid octal numbers, but instead of raising an exception, the conversion code treats them as 0's.

I bet if you change your query to

@Query("""SELECT cast(strftime('%m', timestamp / 1000, 'unixepoch') as integer) as month,
                 COUNT(*) as count
          FROM ${EventLog.TABLE_NAME} 
          GROUP BY month""")

you'll get the expected results.

Upvotes: 2

Related Questions