android - SQLite group by/count hours, days, weeks, year -

assume have simple android application has 1 button.
click on button record date , time of click (in sqlite).
wondering best format records keep long or string of date-time.
purpose make reports show:
1. sum of clicks made chosen day grouped hours.
2. sum of clicks made chosen week grouped days.
3. sum of clicks made chosen month grouped weeks.
4. sum of clicks made chosen year grouped month.

how create such database , how do such queries?

i'd vote using unix timestamps (number of seconds since "epoch"), given they're convenient range calculation , understood robust date-time libraries.

sqlite provides few helper functions working unix timestamps. useful 1 here going strftime.

you can insert current unix timestamp using strftime('%s', 'now') in insert.

later, if know particular time range you're interested in, can calculate minimum , maximum timestamps range , select rows between them:

select * data  timestamp >= strftime('%s', '2012-12-25 00:00:00')  , timestamp < strftime('%s', '2012-12-25 01:00:00'); 

or, supposing want count year's requests month:

select strftime('%m', timestamp), count(*) data timestamp >= strftime('%s', '2012-01-01 00:00:00')  , timestamp < strftime('%s', '2013-01-01 00:00:00')  group strftime('%m', timestamp); 

through clever use of format options strftime provides, can work out of queries pretty quickly.