CODEWITHSUNDEEP
selectgroup-bytimestampinfluxdb
David Bellot
David Bellot

Reputation: 671

Get the last timestamps in a group by time query in Influxdb

I have a database with price and timestamps in nanoseconds measurement in InfluxDB. When I do a select grouped by time like this one:

select first(price),last(price) from priceseries where time>=1496815212834974866 and time<=1496865599580302882 group by time(1s)

I received a time column in which the timestamps is aligned to the second beginning the group. For example, the timestamp will be 08:00:00 and the next timestamps will be 08:00:01

How to

  1. apply aggregation function on the record timestamps itself like last(time) or first(time) so that to have the real first and last timestamps of the group (I can have many prices within my group) ?
  2. and how the time column in the response could be the closing second and not the opening second, that is if the group goes from 08:00:00 to 08:00:01, I want to see 08:00:01 in my time column instead of 08:00:00 which I see now ?

Upvotes: 2

Views: 3487

Answers (1)

danny
danny

Reputation: 5270

  1. Not when using an aggregation function, which implies use of group by.

select first(price), last(price) where time >= <..> and time <= <..> will give you the first and last price within that time window.

When the query has a group by, the aggregation applies only to values within the intervals. The values themselves are the real values that fall in the 08:00:00 - 08:00:01 interval, it's just that the timestamp shown is for the interval itself, not the actual values.

Meaning that the query for between 08:00:00 and 08:00:01 without a group by and the query with a group by time(1s) for same period will give same result. Only difference is query without group by will have the value's actual timestamp and the group by query will have the interval's timestamp instead.

  1. The timestamp when using group by indicates the starting time of the interval. From that, you can calculate end time is start time + interval. What timestamp to show is not configurable in the query language.

Upvotes: 3

Related Questions