How does it show data on day of week?

Hi,
I would like to pick up data on every Friday.
for example;
2021/7/2, 2021/7/9, 2021/7/16, 2021/7/23, 2021/7/30

I tried below query, but it does not work.
How do I write query?

import “date”

from(bucket: “my_bucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “target”)
|> filter(fn: (r) => r["_field"] == “val”)
|> aggregateWindow(every: date.Friday, fn: last, createEmpty: false)
|> yield(name: “last”)

Hello @mm0001,
I don’t exactly know what you mean by pick up data on every Friday.
Here’s how I filtered for data that’s only written on a Friday and then performed an aggregate window.

import "date"
from(bucket: "noaa")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "average_temperature")
  |> filter(fn: (r) => r["_field"] == "degrees")
  |> filter(fn: (r) => r["location"] == "santa_monica")
  |> map(fn: (r) => ({ r with weekday: date.weekDay(t: r._time)}))
  |> filter(fn: (r) => r.weekday == date.Friday)
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Hi, @Anaisdg,

Thank you for your reply!
Your reply is the solution I was looking for.

I have 2 questions for your query.

  1. I would like to show max value.
    Therefore, I change to below script.
    Then, it shows _value and weekday.
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
  |> yield(name: "max")
  1. I live in UTC+9h location.
    How do I change timezone of influxdb?
    or How do I write query with timezone UTC+9h?
    I ran your script, then, time is 2021/7/31 09:00:00.
    Emvironment : Ubuntu on docker → it is UTC+9h.
    Host and Guest OS : windows 10 → It is UTC+9h

Hello @mm0001,
I’m sorry I don’t understand the your question 1. It seems like you answered your questions?

If you save the query to a dashboard cell you can select between local time and UTC:
https://docs.influxdata.com/influxdb/v2.0/visualize-data/dashboards/control-dashboard/#select-timezone
This button also exists in the data explorer.

Hi @Anaisdg,

Thank you for your reply.
I forgot to write influxDB version.
InfluxDB 2.0.7

  1. It may depend on Grafana.
    I will confirm it to Grafana.

  2. Please refer to below chart.
    last data of upper chart is 8.51k.
    however, lower chart is 575.
    I think time range on every of lower chart is 9:00 am 23/Jun to 9:00 am 24/Jun as local time.
    It means range is UTC 0:00 am 23/Jun to 0:00 am 24/Jun.
    I would like to set time range to 0:00 am 23/Jun to 0:00 am 24/Jun as local time.

Query of upper chart;

import "date"
from(bucket: "my_bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "test1")
  |> filter(fn: (r) => r["_field"] == "target")
  |> filter(fn: (r) => r["Location"] == "test")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> yield(name: "last")

Query of lower chart;

import "date"
from(bucket: "my_bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "test1")
  |> filter(fn: (r) => r["_field"] == "target")
  |> filter(fn: (r) => r["Location"] == "test")
  |> map(fn: (r) => ({ r with weekday: date.weekDay(t: r._time)}))
  |> filter(fn: (r) => r.weekday == date.Friday)
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> yield(name: "last")

Hello @mm0001,
It looks like you’re already querying from local time. To change the range to:

I would like to set time range to 0:00 am 23/Jun to 0:00 am 24/Jun as local time.

switch the time date picker to reflect that time instead of 01/07/21 at 1:00 as shown from your screen shot (I cant see the end time as it’s cut off). In the top right corner.

Alternatively you can put the dates directly into the flux query:
|> range(start: 2021-07-23T00:00:00Z, stop: 2021-07-24T00:00:00Z)

If I’m misunderstanding you and the problem isn’t that you cant select the right range but that you’ve written the data with the wrong timestamp, you might want to use the timeShift() function
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/timeshift/

Hi, @Anaisdg,

Thank you for your reply.
I used timeShift() function, I could set UTC+9h .

1 Like