Group time by date

#1

hi, i’d like to get a count of records per day/month. how can i do that? thanks.

#2

Hi,

I would suggest you to make a request with a WHERE close, with a timestamp range.
Remember that the timestamp values can change drastically according to the time precision you set.

Such as :

SELECT *
FROM measurement
WHERE time > ##timestamp value here##
AND time < ##other timestamp value here##

#3

thanks for taking the time but this doesn’t answer the question.

i need something like select distinct(date_part(time)), count(1) from tick group by date_part(time)

and a result like

1-1-2001, 100
2-1-2001, 200

#4

I think influxDB doesn’t support that kind of functions right now.

But you can very easily do the same in your code. You just have to use timestamp & convert it to have the range you want. Well, maybe you will have to do more requests but at least it’s possible.

Example :
SELECT COUNT(time)
FROM measurement
WHERE time > ##timestamp value here##
AND time < ##other timestamp value here##
GROUP BY time

And with your timestamps values you can specify the range (1 day, 1 week, 1 month, whatever).
And yeah it’s not perfect, you would have to do one request per day with my solution, but i don’t have any better to offer you.

You may find something usefull in your case here

You may also want to check the advanced group by time syntax in the doc.

#5

again, thanks for the answer. that kind of solved :slight_smile: it took unexpectedly -considering this is a time series db- long time even with 20 days of data in db and got this result…


1504224000000000000 8524737
1504310400000000000 3679264
1504396800000000000 3588612
1504483200000000000 7309624

issuing “precision RFC3339” formatted the results to

2017-09-01T00:00:00Z 8524737
2017-09-02T00:00:00Z 3679264

I think this solved my problem, thank you very much.