Select time difference to show how long is the daylight

InfluxDB 1
,
1

Hi eveyone, this is my first topic.
I have a time serie of just integers of illumination (LUX) from sensor.
2022-01-12 07:37:00 8
2022-01-12 07:38:00 13
2022-01-12 07:39:01 14
2022-01-12 07:40:02 16
2022-01-12 07:41:03 25
2022-01-12 07:42:03 31
…

I would like to select the time interval between sunrise and sunset to show how many hours is the daylight
I’ve tryed with this: SELECT ELAPSED(“illuminance”, 1h) FROM “daylight” WHERE “illuminance” >= 10 AND time >= now() -120d.
Obviously not working!
thank you for your help.

2

Hi again, hope to animate discussion giving more detail about the specific question.
Luminosity sensor send simpel numeric data via MQTT to a Raspberry based InfluxDB instance. The Measurement look like:
time illuminance

1646043512659935879 1798
1646043572824785751 1796
1646043633104567419 1798
1646043693198425494 1806
1646043753386741421 1812
…

During the night values are significantly low, like:
time illuminance

1646000913110873557 2
1646000973821686836 2
1646001034525752818 2
1646001095247263060 2
1646001155949593727 2
…

Grafana panel showing data of the last week, looks like this:

Screenshot 2022-02-28 120439
Screenshot 2022-02-28 120439929×346 51.7 KB

where the green line show day and night, the yello one top values and cyan line the mean value.

We know days are shorter in winter and longerin summer (considering the total time of light), during spring the ammount of daytime increase and in autumn decrease.

Thefirst goal is to calculate the total time between last sunset and sunrise, ELAPSED() looks usefull but I do not figure out how to apply it.

After that I would like to plot a graph showing the daitime incrasing and decreasing…
May someone had same goal or can help me?

Thank you!

3

Hello @Francesco_Giunta,
What version of Flux are you using?
I think this would be possible with 2.x and Flux but I’m not sure about InfluxQL (might be possible for you to enable Flux in 1.8 but Flux is much more limited in 1.x).

Off the cuff, I would probably use the following functions in Flux:

  1. map to assign a level (night or day) with conditional query logic if the value is greater or less than a threshold
    Query using conditional logic in Flux | InfluxDB OSS 2.1 Documentation
  2. stateduration to calculate how long it was day or night
    stateDuration() function | Flux 0.x Documentation
4

Hi @Anaisdg,

Yes I’m using InfluxDB shell version: 1.8.10 and my queries are made in Grafana.
I can upgrade InfluxDB version to 2 and learn how to work throug Flux.

Thank you!