CODEWITHSUNDEEP
javaandroidsqlite
Saurabh Kumar
Saurabh Kumar

Reputation: 2367

sql query to average, after grouping by date part of millisecond

In my android application, I am using sqlite to store rows of data. More than one rows would be there per day, and on each row there is a datetime stored in milliseconds too.

e.g.

millis, col1, col2, num
xxxx,   abc,  xyz,  24
xxxx,   abc,  xyz,  24
xxxx,   abc,  xyz,  24
xxxx,   abc,  xyz,  24

Can I have a query which can group the millis column by day(that is 24th, 25th, 26th etc.), and give my average of the num col?

e.g. output:

    Date         avg
    2013-11-08   20
    2013-11-07   24

Upvotes: 1

Views: 736

Answers (2)

CL.
CL.

Reputation: 180210

SQLite's Unix Time format is in seconds, you have to divide by 1000 first. Then you can use one of the date functions to get the date portion of the timestamp:

SELECT DATE(millis / 1000, 'unixepoch') AS Date,
       AVG(num) AS avg
FROM MyTable
GROUP BY Date

Upvotes: 2

Karl
Karl

Reputation: 175

Use the from_unixtime to convert your milliseconds to a date and then wrap that value into the DATE() function to keep only the date part ignoring hours, minutes and seconds.

The rest of the query is trivial.

  SELECT DATE(FROM_UNIXTIME(millis)) AS date,
         AVG(num) AS average
    FROM table_name
GROUP BY date

Upvotes: -1

Related Questions