问题描述:

I have a table that has time in unixtimestamp (ms), I want to convert time only to day and group by day with aggregated function COUNT. I tried this but it didnt work:

SELECT

strftime('%d',(timestamp+1465876799998)/1000, 'unixepoch') a

COUNT(strftime('%d',(timestamp+1465876799998)/1000, 'unixepoch'))

FROM

clicks_train_

GROUP BY

strftime('%d',(timestamp+1465876799998)/1000, 'unixepoch')

ORDER BY strftime('%d',(timestamp+1465876799998)/1000, 'unixepoch') DESC

LIMIT 10;

网友答案:

Just use strftime with a format specifier to the day:

SELECT strftime('%Y-%m-%d', timestamp / 1000, 'unixepoch'),
       COUNT(*)
FROM clicks_train_
GROUP BY strftime('%Y-%m-%d', timestamp / 1000, 'unixepoch')
ORDER BY strftime('%Y-%m-%d', timestamp / 1000, 'unixepoch') DESC
LIMIT 10;
相关阅读:
Top