Add a sensor chart box for the dashboard

[Pierre-Gilles]

Description

Be able to display a chart on the dashboard. Ex: temperature of last month

Specs

• [ ] User can create a chart box on the dashboard
• [ ] The user select the device feature he wants to display in the list of "read_only" device feature. Only interesting device features should be displayed. Binary should not be displayed for ex
• [ ] The user select a range between a select box. (Last 24h, last 2 days, last week, last month)
• [ ] The box displays the chart, he a nice way. Take inspiration on the homepage of our theme => https://preview.tabler.io/

[euguuu]

it's ok for you if I try to do this ticket or you already start dev for this ?

[VonOx]

@atrovato is assigned from 16 May but no PR , I think you can !

[atrovato]

I confirm, I let you this one.

[Pierre-Gilles]

Let's talk about the chart library you use before starting the dev !

I'm really in favor of using small (not too many deps) and well maintained library for the frontend.

I like https://bundlephobia.com/ to measure a library size.

[atrovato]

I don't have anything to share on this, but we already use a chart lib, maybe this is enough https://visjs.org/

[Pierre-Gilles]

Honestly I don't really like this lib :D I want to remove it from Gladys ^^

In Preact we do code splitting per page, so the visjs file is only downloaded when you enter the "Z-Wave / network" page. I don't think we want this big library on the dashboard which is the main page.

I think we should cherry-pick a tiny library for this to avoid huge page load on the first page.

[Pierre-Gilles]

I like chartjs: https://www.chartjs.org/docs/latest/

And it seems it's tiny (no external dependency): https://bundlephobia.com/result?p=chartjs@0.3.2

Edit: see below I mistyped chart.js

[Pierre-Gilles]

Ok my bad, I typed the wrong module on bundlephobia, I type "chartjs" instead of "chart.js"...

chart.js is big:

[Pierre-Gilles]

There is the famous d3.js library which is a little big too but I read that the library is tree-shakable, which means that maybe it could be possible to import only what we need in the lib! ( Read more about Tree-shaking )

I found victory too: https://github.com/FormidableLabs/victory

And it's tree-shakable !

[Pierre-Gilles]

I really like nivo too: https://nivo.rocks/line/

We could even use it do to presence/absence, heating on/off tracking in Gladys:

[Pierre-Gilles]

Ok I remembered that the theme we are using in Gladys 4 (tabler) is doing chart as well!

Example:

Example 2:

I think they are using https://apexcharts.com/ https://github.com/apexcharts/apexcharts.js, which is open-source too.

It looks nice!

[Pierre-Gilles]

To recap:

Library	Pros	Cons
ApexCharts	Well integrated with our theme. Really beautiful. Lots of charts possible.	118.2kB bundle size gzipped
Chart.js	Beautiful & rock solid. Been here for a long time	123.8 kB size gzipped
Nivo	Beautiful & large collection of charts. Each component can be loaded independently. 83.8 kB gzipped for the line component. Tree-shakable	Huge library to understand. I didn't get if 83kb is enough to have the whole line library working?
Victory	Tree-shakable. Large range of charts available	Not beautiful out of the box, we need to customize it

This is my first results after investigating during 45 minutes, I'm open for recommandation which fits our needs :)

Also, testing each lib can be a good idea to see the impact/ease of use of each of them.

[euguuu]

In my opinion, charts integration in gladys matches to:

principal :

• display historical data of home sensors
• display historical data of user health

secondary:

• display interaction between domotic hardware (like network in zwave)
• help to optimize home management (like electricity consumption)
• help to optimize health management (like sport session, km in time for runner)

for the moment feature state coresponds to timeseries data , I am not sure this point needs to evoluate, maybe position used with owntrack and user presence can open another data point of view

for those needs I think those chart types match:

• line / area
• column / bar
• timeline
• pie
• network
• bubble

=> I don't see network chart type allowed in chartjs, apexcharts and victory => Nivo has all we need

In terms of documentation, Nivo appears more complicated (no big documentation) chartjs, apexcharts and victory seems good (big documentation and get started seems easy)

In terms of look & feel, apexcharts default view appears closest to gladys victory and nivo are very distant to gladys theme => I think to much effort need to build something like ladys (except if a real designer is available in gladys community)

So for me the winner is apexcharts (but chartjs is just behind and perenity of this lib can be an important point) for network chart we cn continue with visjs if it's impossible with apexcharts or chartjs.

[Pierre-Gilles]

So for me the winner is apexcharts (but chartjs is just behind and perenity of this lib can be an important point)

I agree with you. Can you do a quick proof-of-concept of apexcharts? (in a separate PR)

By the way, just a point on the feature. You'll have to do a backend route to get device feature states. Be careful not to return all values, you can do something called "downsampling" (Wikipedia if you are not familiar with the concept)

For example, if the user wants to display the last week of data and his sensors is saving 1 value/minute, it means there is 10k values in the database for 1 week of data, and it's probably too much to display on the frontend + it'll take too much time to download a 10k rows array in the API.

So you can use something like this NPM package (https://www.npmjs.com/package/downsample, to be tested) backend-side to reduce the 10k elements array to something with less value (like 50? 100? to be tested in the UI).

Does it make sense to you ? :)

If you have question don't hesitate. The NPM package I recommended for downsampling is just the first one I found on NPM, so if you find a better one (more stars, more usage), you can definitely use another one :)

[euguuu]

ok

[euguuu]

how can I change the status of this issue to put it 'in progress' ?

[Pierre-Gilles]

Maybe simply create a WIP PR with a link to this issue ? It'll display a message in this issue

[VonOx]

You are assignee and for linking issue to PR you use theses keywords

https://docs.github.com/en/free-pro-team@latest/github/managing-your-work-on-github/linking-a-pull-request-to-an-issue#linking-a-pull-request-to-an-issue-using-a-keyword

[euguuu]

I put a PR linked to this issue with first version of poc (you need t put a 'device in room chart' box in edit mode, no need to specify something in form all data are fake)

[Pierre-Gilles]

Nice!

Can you see if you can get the same beautiful design we can see on tabler.io website? (like here: https://github.com/GladysAssistant/Gladys/issues/693#issuecomment-730256467)

[euguuu]

in fact is very easy to do same design of tabler.io because it uses apexchart to display graph so it's just a copy of chart options to do the same

example integrate in gladys:

[Pierre-Gilles]

I was talking about this design:

Can't we do something more "edge to edge" ?

[euguuu]

this design is the same of area screenshot I do (without line in background and no toolbar), the box with "Today's Earning ..." is just HTML component, not a part of chart, so it's possible to do that I work on it for next step of this dev.

[euguuu]

I try to do this render but I have some issue

I try to create 2 types of box : 1- one with only 1 feaure display 2- one with multiple features display

1- I try to reproduce

but for the moment I do this:

=> I don't understand why but I can't change height of chart and dropdown list doesn't work

2- This box is just to display any features for any room so I want do the same box with legend and a "fullscreen" link, in first step I try to factorize code and apply same method of deviceInRoomBox but I don't understand why chart don't refresh data (Ithink I miss something in life cycle of props/state/store/blablabla...)

Somedbody can help me about those issues ?

(I need to add some config for tooltip design and add loading on box but it's not difficult but I need to make a break on front part ;-) )

[Pierre-Gilles]

Looks really nice!

Just one feedback:

I don't understand why but I can't change height of chart and dropdown list doesn't work

So you use the React-apexchart library and the height={} props doesn't work?

I need to make a break on front part ;-)

Sometimes all we need is a break to see the issues when coming back :D

[euguuu]

I do some change on this issue

• For One feature chart: ** EditBox:

** Dash Box:

• For multi features chart: ** EditBox:

** Dash Box:

=> I wait your feedback to change anythings.

actually I exclude those type of feature to feature list (for both): const excludeFeatyreType = [ DEVICE_FEATURE_TYPES.SENSOR.BINARY, DEVICE_FEATURE_TYPES.CAMERA.IMAGE, DEVICE_FEATURE_TYPES.SENSOR.UNKNOWN, DEVICE_FEATURE_TYPES.UNKNOWN.UNKNOWN, DEVICE_FEATURE_TYPES.LIGHT.COLOR ]; => Any others type ?

TODO:

• [optionnal] put screenshot of chart type in edit box select
• improve design of column chart ( principaly for multi feature display )
• add link to open multi feature chart in large screen (I try to use modal button describe in this page https://preview.tabler.io/modals.html but doesn't work, there are any example of modal box in gladys ?)
• improve server test
• challenge downsample & trend algo

[VonOx]

Wow it's beautiful. I think binary sensor must be added, a user must see when door is open or closed over a day ( just use case). No sampling in that case

[Pierre-Gilles]

@VonOx let's keep binary sensor for later? One step at a time, we don't want a monster PR :)

I think we want to treat binary a different way, we want to display a specific chart for that, like:

[VonOx]

Yes I agree this kind of graph is very useful, we can implement later on a good base.

[Pierre-Gilles]

@euguuu Thanks for your work it's really nice! :)

First on the design, I like it but I think we could be more "edge to edge". Could you remove all padding on the boxes so there is no blank border?

For the exclude list, you can exclude the cube too:

CUBE: {
    MODE: 'mode',
    ROTATION: 'rotation',
  },

And the button too:

BUTTON: {
    CLICK: 'click',
  },

What's the "choose subset of devices" ? I don't find the UI very explicit.

there are any example of modal box in gladys ?)

We don't do modals in Gladys as it's not really a good practice when your Webapp is also a mobile app. Modals looks great on desktop (it looks like windows in a windows), but on mobile it makes the flow complicated. For now, I haven't seen any use case that was really requiring a modal.

challenge downsample & trend algo

Which algo did you use at the end?

[euguuu]

for reduce number of object returns I use downsample: https://www.npmjs.com/package/downsample , the cgladys ontroller can accept a parameter to choose algo (ASAP , SMA , LTTB , LTOB , LTD ), now I use LTTB with 1000 objects , I need more time to test all algo and choose the best for representation/performance and maybe adapt default number values return

for trend (only the little green or red arrow => one value return to front) I use https://www.npmjs.com/package/trend , I do'nt find better one, I'm not sure is very usefull in this box

[Pierre-Gilles]

for reduce number of object returns I use downsample: https://www.npmjs.com/package/downsample , the controller can accept a parameter to choose algo (ASAP , SMA , LTTB , LTOB , LTD ), now I use LTTB with 1000 objects

Ok! When you find the best algorithm, just set it in your function and remove the controller parameter, this is not really something needed client side, let's keep the API as clear as possible and with the best default :)

for trend (only the little green or red arrow => one value return to front) I use https://www.npmjs.com/package/trend

I like the trend and I think we want to do keep it, but this library is not something we can keep in Gladys, it's outdated and not supported anymore:

I think the trend is something we should calculate ourself.

Let's get back to the use case: When we display a trend, the goal is to understand how the value is evolving.

Example:

The humidity is 90% right now, it was 70% one hour ago.

V% = (90-70)/70*100 = +29%

Now the main question is: which values are we comparing?

I think we could compare the first value of the interval displayed by the user with the last value of the interval (the current value of the sensor)

If the user displays the past hour of temperature on his dashboard, it means we could compare the value from 1 hour ago (first value of the chart), to the current value (last value of the chart)

What do you think of that?

[euguuu]

Downsample tests:

All of the algorithm use a chartWidth to limit number of results but it's not a strict limit for ASAP (result is very close to limit) & SMA (result is very far to limit)

Algo theory:

• LTOB:This algorithm is very simple. First all the points are ranked by calculating theireffective areas. Points with effective areas as null are excluded. The data pointsare then split up into approximately equal number of buckets as the specified down-sample threshold. Finally, one point with the highest rank (largest effective area) isselected to represent each bucket in the downsampled data.
• LTTB: same of LTOB but treat 3 points in comparison area (improve precision)
• LTD: same of lTTB but the range for comparison area is calculated dynamicaly (improve precision but more time need on calcutation [server side))
• SMA: In statistics, a moving average (rolling average or running average) is a calculation to analyze data points by creating a series of averages of different subsets of the full data set. It is also called a moving mean (MM) or rolling mean and is a type of finite impulse response filter. Variations include: simple, and cumulative, or weighted forms (described below). => I'm not really understand the variation style use in this algo (I try to do more test with different chartWidth to understand) => normally this algo must be goo for representation but very difficult to ensure the number of results returns
• ASAP: automatically smoothes time series plots to remove short-term noise while retaining large-scale deviations.

TESTS:

I try 2 different dataset with 1 value by minutes => 44 640 values for 31 days (first size value is transfer value and second value after decompression I suppose, extract with console of firefox) (with 44 640 values I never see representation , I don't undestand why I try to fix this)

Test 1: Dataset : This dataset use random value between 1 and 50 (worst case for representation)

• LTOB: chartWidth 1000 : 6,21 Ko (taille 39,75 Ko)

chartWidth 500 : 3,75 Ko (taille 20,41 Ko)

chartWidth 250 : 2,48 Ko (taille 10,79 Ko)

chartWidth 100 : 1,63 Ko (taille 5,00 Ko)

chartWidth 50 : 1,31 Ko (taille 3,07 Ko)

• LTTB: chartWidth 1000 : 6,09 Ko (taille 39,72 Ko)

chartWidth 500 : 3,68 Ko (taille 20,43 Ko)

chartWidth 250 : 2,28 Ko (taille 10,77 Ko)

chartWidth 100 : 1,58 Ko (taille 5,00 Ko)

chartWidth 50 : 1,30 Ko (taille 3,07 Ko)

• LTD: chartWidth 1000 : 6,12 Ko (taille 39,72 Ko)

chartWidth 500 : 3,66 Ko (taille 20,43 Ko)

chartWidth 250 : 2,29 Ko (taille 10,79 Ko)

chartWidth 100 : 1,58 Ko (taille 5 Ko)

chartWidth 50 : 1,30 Ko (taille 3,07 Ko)

• SMA: chartWidth 1000 : 190,68 Ko (taille 1,29 Mo) => 43641 values returns

chartWidth 500 : 192,91 Ko (taille 1,30 Mo) => 44141 values returns

chartWidth 250 : 195,04 Ko (taille 1,30 Mo) => 44391 values returns

chartWidth 100 : 193,17 Ko (taille 1,27 Mo) => 44591 values returns

chartWidth 50 : 189,01 Ko (taille 1,27 Mo) => 44591 values returns

• ASAP: chartWidth 1000 : 8,25 Ko (taille 39,22 Ko) => 915 values returns

chartWidth 500 : 4,85 Ko (taille 19,92 Ko) => 452 values returns

chartWidth 250 : 3,04 Ko (taille 10,54 Ko) => 226 values returns

chartWidth 100 : 1,96 Ko (taille 4,92 Ko) => 91 values returns

chartWidth 50 : 1,50 Ko (taille 3,06 Ko) => 46 values returns

Test 2: Dataset : This dataset use increase value between 1 and 50 (value return to 1 evrey 50 min)

• LTOB: chartWidth 1000 : 4,14 Ko (taille 40,12 Ko)

chartWidth 500 : 2,67 Ko (taille 20,67 Ko)

chartWidth 250 : 2,00 Ko (taille 10,91 Ko)

chartWidth 100 : 1,43 Ko (taille 5,05 Ko)

chartWidth 50 : 1,20 Ko (taille 3,10 Ko)

• LTTB: chartWidth 1000 : 4,64 Ko (taille 39,74 Ko)

chartWidth 500 : 2,94 Ko (taille 20,43 Ko)

chartWidth 250 : 1,85 Ko (taille 10,79 Ko)

chartWidth 100 : 1,43 Ko (taille 5,00 Ko)

chartWidth 50 : 1,25 Ko (taille 3,07 Ko)

• LTD: chartWidth 1000 : 4,71 Ko (taille 39,74 Ko)

chartWidth 500 : 2,93 Ko (taille 20,42 Ko)

chartWidth 250 : 1,85 Ko (taille 10,79 Ko)

chartWidth 100 : 1,43 Ko (taille 5,00 Ko)

chartWidth 50 : 1,24 Ko (taille 3,06 Ko)

• SMA: chartWidth 1000 : 111,79 Ko (taille 1,21 Mo) => 43641 values returns

chartWidth 500 : 113,05 Ko (taille 1,22 Mo) => 44141 values returns

chartWidth 250 : 113,76 Ko (taille 1,23 Mo) => 44391 values returns

chartWidth 100 : 114,16 Ko (taille 1,23 Mo) => 44541 values returns

chartWidth 50 : 114,31 Ko (taille 1,23 Mo) => 44591 values returns

• ASAP: chartWidth 1000 : 4,97 Ko (taille 40,78 Ko) => 1014 values returns

chartWidth 500 : 3,39 Ko (taille 20,45 Ko) => 470 values returns

chartWidth 250 : 2,24 Ko (taille 10,67 Ko) => 233 values returns

chartWidth 100 : 1,65 Ko (taille 5,26 Ko) => 100 values returns

chartWidth 50 : 1,41 Ko (taille 3,03 Ko) => 46 values returns

Conclusions:

Performance (weight of result only): => (approximately) 1000 values = 6 ko ; 500 values = 3k ; 250 = 2.5ko ; 100 = 1.5 ; 50 = 1.3 => I will try to limit the size (limit value to 1 decimal, check date format, remove device or features informations not really need, any others idea ?) => In my opinion, 100 values returns is the good numbers

Visualization: => In my opinion, with those tests, I think LTD is the best to see a good representation of dataset, you see min max value and variation is not each time invisible => LTOB and ASAP are to far of real dataset => I want play more with SMA to check others parameter => I want compare with more "light" algo, for example extract only min max by day (maybe I can reduce weight with same visualization) => Another solution can be to display different drill on X axis (=date), for example display min max by week if the selector is on month or year , it is more complicated for dev in front side and I'm not sure correspond to each use case

[Hamtar0]

And on mouse over, we can see values and dates ?

[Pierre-Gilles]

@euguuu Thanks for this very complete analysis!

Size: I'm ok with 100 values. If the number of values recorded in the interval is less, you agree that the downsampling won't be needed right? Performance: How fast is the backend to go through the whole controller ? (GET SQL + downsampling?)

I want compare with more "light" algo, for example extract only min max by day (maybe I can reduce weight with same visualization)

I think you'll have better results with those algorithm,+ you'll have to think of every filtering possible (by hour, by 24h, by week, by month) don't waste too much time on this, LTD is great :)

[euguuu]

And on mouse over, we can see values and dates ?

On tooltip you see date and value but only on 100 values go up

[euguuu]

@euguuu Thanks for this very complete analysis!

Size: I'm ok with 100 values. If the number of values recorded in the interval is less, you agree that the downsampling won't be needed right? => yes is alerady the case

Performance: How fast is the backend to go through the whole controller ? (GET SQL + downsampling?)

=> I check it latter

I think you'll have better results with those algorithm,+ you'll have to think of every filtering possible (by hour, by 24h, by week, by month) don't waste too much time on this, LTD is great :) => ok

[euguuu]

Performance: How fast is the backend to go through the whole controller ? (GET SQL + downsampling?) => the time on server side is ~ 5s for 100 values (downsampling LTD ) => I reduce weight of 100 values to 1Ko

[VonOx]

@euguuu seconds or milliseconds?

[euguuu]

is 5 seconds, it's long, how can I build a raspberry img to test on raspberry ?

I remove some padding in box:

about trend, I'm not sure comparison between 2 values describe a real trend, I think we need to take all data values display . hat do you think about those lib: https://www.npmjs.com/package/basic-trend https://www.npmjs.com/package/regression https://www.npmjs.com/package/simple-statistics https://www.npmjs.com/package/regression-trend

[Pierre-Gilles]

Performance: How fast is the backend to go through the whole controller ? (GET SQL + downsampling?) => the time on server side is ~ 5s for 100 values (downsampling LTD )

5 seconds? wow, it's way too long. It expected something like 100ms maximum. What's slow? The SQL query? The downsampling?

Let's investigate what's wrong because 5 sec is way too long.

about trend, I'm not sure comparison between 2 values describe a real trend, I think we need to take all data values display . hat do you think about those lib:

Why? This is how most trading app works. See for example: https://finance.zacks.com/calculate-daily-price-variation-stocks-8299.html

From that link:

if a stock opened at $75 per share, fell to $70 and remained there until the end of the day, you would divide $5 (the daily price variation) by $70 (the closing, or current, price) for a result of .071, representing a 7.1% daily price variation.

Let's not use a library to display something as simple as a variation :)

[euguuu]

I work on performance aspect, I test on database with 100 800 rows repartee on ~ 2 month 1 month of data represent 44 640 rows

Before changes:

• Total Time : 6875 ms
• SQL time : 3170 ms
• Downsampling time : 3705 ms

After changes:

• Total Time : 769 ms
• SQL time : 744 ms => remove some attribute in select, do 2 different request (1 for get device/room/feature info and 1 for feature state) instead of 1 request with join
• Downsampling time : 25 ms => change algo to LTTB

(I change trend method to use last/first value without performance impact)

I try to find a solution to increase performance of sql request used to extract feature state ...

how can I build a raspberry img from my branch to test on raspberry ?

[Pierre-Gilles]

Very nice improvements, congrats 👏

I'm sure we can go even further in the SQL request :) What's the current SQL request?

how can I build a raspberry img from my branch to test on raspberry ?

You can use the GitHub Action in the repo (https://github.com/GladysAssistant/Gladys/blob/master/.github/workflows/docker-dev-build.yml)

You need to set DOCKERHUB_USER, DOCKERHUB_PASSWORD and change DOCKERHUB_REPO, then you can trigger the workflow and it'll build and push the image to your DockerHub account with the :dev tag.

Edit: Or you could do it manually using the same command as in the GitHub action script, but locally on your computer :)

[euguuu]

there are 2 requests: first (~ 10ms): SELECT t_device.id, t_device.name, t_device.selector, features.id AS features.id, features.name AS features.name, features.selector AS features.selector, features.unit AS features.unit, features.last_value AS features.last_value, features.last_value_changed AS features.last_value_changed, room.id AS room.id, room.name AS room.name, room.selector AS room.selector FROM t_device AS t_device INNER JOIN t_device_feature AS features ON t_device.id = features.device_id AND features.selector = 'test-temperature-sensor-2' LEFT OUTER JOIN t_room AS room ON t_device.room_id = room.id LIMIT 0, 10000000000000

seconds (~ 700 ms): SELECT device_feature_id, value, created_at FROM t_device_feature_state AS t_device_feature_state WHERE t_device_feature_state.device_feature_id = 'f07c5b27-9301-4482-a059-9f91329d30e7' AND t_device_feature_state.created_at BETWEEN '2020-12-09 15:19:26.160 +00:00' AND '2021-01-09 15:19:26.160 +00:00' ORDER BY t_device_feature_state.created_at ASC

I put those 2 request in same transaction and I change index I put in t_device_feature_state to put a new index on column device_feature_id + created_at to improve explain plan Now explain plan is: SEARCH TABLE t_device_feature_state AS t_device_feature_state USING INDEX t_device_feature_state_device_feature_id_created_at (device_feature_id=? AND created_at>? AND created_at<?) => only one line in explain, use index for where and order by => those change win a couple ms but not significant

So I change sequelize call of request 2 (because I think it's more sequelize pb, when I execute this request in my sql client I have ~2 ms of exec and ~ 180ms to display) now I use ~ same query but in raw query => I think is best solution (is often the case with ORM) but not in code design, do you agree with raw query usage in gladys (i do not find any occurrence of this method in another request)? => This method improve performance and now I have result in ~220ms ( total time : ~ 300ms)

Now I don't know how I can improve performance (I try soon on raspberry to know time in real target).

I find those articles : https://stackoverflow.com/questions/41965054/best-data-type-for-sqlite-index https://stackoverflow.com/questions/17277735/using-uuids-in-sqlite https://mareks-082.medium.com/auto-increment-keys-vs-uuid-a74d81f7476a => maybe UUID column do not help in index performance but I'm not sure about that and is big change => I do not find any information about different type of index, do you know if SQLite have another index type ? (in some database btree index on datetime column is not the best) => I think the time is lose in serialize data and not in sql request execution, do you know if it's possible to fix fetch size with sequelize ? (I'm not sure this config exist in javascript database driver but this config often increase fetch in Java)

[euguuu]

I have this error when I try to build img, what I do wrong ?:

[VonOx]

Because DOCKERHUB_USER AND/OR DOCKERHUB_PASSWORD is not set in repo secrets.

[Pierre-Gilles]

@euguuu Nice investigation! Are you sure that sequelize produce a different SQL code than your raw SQL ? If possible, I would like to avoid writing raw SQL, because it'll make it difficult in a near future to configure Gladys with different DB engine (postgresql, mysql, ...)

Your SQL query is really simple and I'm sure we could have the same query with Sequelize! What's your sequelize code?

[euguuu]

sorry for misunderstood, sql reqest is same with sequelize (i just remove some quote en table prefix)

but when you do this request with sequelize the json object contain more information (about transaction, and others things but not in real content) and with raw we have directly the content (i do not need to do second action plain to get object)

I send you code after, but clearly sql request is the same (and I think perfomance is not loose in sql execution)

PS: I'm very happy to see we can think to change with most powerfull database like postgres (in this case datbase engine change do not impact raw query)

[Pierre-Gilles]

Hi! Just found this today, have you tried this:

[euguuu]

I apply a downsampling of state in periodic job

now for display last month of date (1 state by minute): => return 3011 raw from state_light and 1300 from state => total time of call ~ 80ms (70 ms sql + 10 downsampling)