# No Timestamp if i summary Data

**URL:** <https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705>\
**Category:** InfluxDB 2\
**Created:** [December 12, 2022, 1:35pm UTC](https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705 "2022-12-12T13:35:12Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Starfoxfs](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/starfoxfs/32/11790_2.png) [@Starfoxfs](https://community.influxdata.com/u/Starfoxfs)\
**Post date:** [December 12, 2022, 1:35pm UTC](https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705/1 "2022-12-12T13:35:12Z")

</div>

Hi,  
I have a problem when I summarize my data. I use Grafana for a Time Series Visualization and this need also a valid Timestamp to add the Data to graphic with formated Timestamps.

```auto
from(bucket: "bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last)
  |> sum()  

```

This is my Query, how can i add here a timestamp of the latest data

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [December 12, 2022, 6:15pm UTC](https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705/2 "2022-12-12T18:15:12Z")

</div>

@Starfoxfs `sum()` is an [aggregate function](https://docs.influxdata.com/flux/v0.x/function-types/#aggregates) and `_time` isn’t in the [group key](https://docs.influxdata.com/flux/v0.x/get-started/data-model/#group-key), so it gets dropped. You can add it back in by duplicating the `_start` or `_stop` columns as the new `_time` column:

```javascript
from(bucket: "bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last)
  |> sum()
  |> duplicate(column: "_start", as: "_time")

```

---

<div class="post-metadata">

**Author:** ![Starfoxfs](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/starfoxfs/32/11790_2.png) [@Starfoxfs](https://community.influxdata.com/u/Starfoxfs)\
**Post date:** [December 13, 2022, 12:01pm UTC](https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705/3 "2022-12-13T12:01:48Z")

</div>

Thanks for the fast Reply,

I have a Solar Power Station, and wan´t to visualize the Consumption for every month and over 1 Year (12 Bars 1 for every month).

The Data that is written in the InfluxDB is the Today Power Consumption but with a Datapoint every Minute.  
So i need every last Datapoint of 1 Day.

I think i need for this 2 Querys because for the first Query what summarize the Data the Time of the Visualization doesnt match.

So that´s my first Query with the Collected Consumption for 1 Month.

```auto
from(bucket: "bucket1")
  |> range(start: -1mo)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> sum(column: "_value")
  |> group (columns: ["_time", "sum"])

```

Now i need a second one to add this first value (sum) to the right Date for this month.

```auto
from(bucket: "bucket1")
  |> range(start: today())
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 30d, fn: last, createEmpty: false)
  

```

I need to join or union the first Query with the seconds \_time to visualize this in the right way.

---

<div class="post-metadata">

**Author:** ![grant1](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/grant1/32/15107_2.png) [@grant1](https://community.influxdata.com/u/grant1)\
**Post date:** [December 13, 2022, 2:19pm UTC](https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705/4 "2022-12-13T14:19:48Z")

</div>

@Starfoxfs

I know you collect a reading every 1 minute, whereas I collect my kWh every hour. See below from January thru August (8 months, or 243 days)

Here is the data by hour (5832 points on the graph, or 243 x 24)

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/1/1dcb95374dc0178a14170342ae25cee11b94e586.png)

Here is the data by day (243 points on the graph)

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/7/77d158022a741e28ad00f8dc85e4237b9552f0d5.png)

Here is the data by month (8 points on the graph):

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/b/ba6bb047ff6229b77d3670a90c383e3ad8b1aa12.png)

Note that I am aggregating the data differently in each of the above. I am using the fn: mean to collect the arithmetic average of the hourly values collected (the average of 24 readings in the 1d aggregateWindow, and the average of all hourly readings during the month in the 1mo aggregateWindow).

You can try the same, but using `fn:sum`

---

<div class="post-metadata">

**Author:** ![Starfoxfs](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/starfoxfs/32/11790_2.png) [@Starfoxfs](https://community.influxdata.com/u/Starfoxfs)\
**Post date:** [December 13, 2022, 2:42pm UTC](https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705/5 "2022-12-13T14:42:36Z")

</div>

I tried this before,

```auto
from(bucket: "bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> yield(name: "sum")

```

With this i gettin wrong Data, i get 453 kilowatt Hours.

Here is a Graphic how it collects data:

 ![collect](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/0/0e3d4f424a266c0778263486c33348b49af8bf34.jpeg)

If i use your Query i have the sum of all Data entrys a day e.g. for the first table on the Picture it is 15 in sum

But that´s wrong, i need every day only the last value, in this case 5 and for the next day 7 and then the sum of all last values over 30days or 1 month

---

<div class="post-metadata">

**Author:** ![Starfoxfs](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/starfoxfs/32/11790_2.png) [@Starfoxfs](https://community.influxdata.com/u/Starfoxfs)\
**Post date:** [December 14, 2022, 12:32pm UTC](https://community.influxdata.com/t/no-timestamp-if-i-summary-data/27705/6 "2022-12-14T12:32:19Z")

</div>

I almost there, how can I make the first table (0) “invisible” ?

```auto
from(bucket: "solarkraftwerk")
  |> range(start: -1mo, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "solarkraftwerk")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last, createEmpty: false)
  |> sum(column: "_value")
  |> duplicate(column: "_start", as: "_time")
  |> group(columns: ["_measurement", "_field", "_time", "_value"])
  |> aggregateWindow(every: 30d, fn: last)

```

 ![Unbenannt](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/e/e3bfd317014003f061243cb7da9d4b6b227f238b.jpeg)