Plot levels of class variables using postgres as data source

I have data like this

obsolete_time product
2010-01-01 java
2012-09-09 python
2015-07-09 c

I would like to plot this using a query in postgres, perhaps a State Timeline that shows for each product their obsolete_time.
So I can have an idea when a product will expire… how can I do this ?

Hi @alexmondaini

Can you create a query (or several queries) that generate data like this?

image

Maybe something like

SELECT product, obsolete_time FROM your_table ORDER BY obsolete_time;

Using data shown above, the State timeline graph looks like this. Not really great, but it gets the point across. Am sure you could tinker with it to show green fill prior to the expiration date and then red after the expiration date.

does this only works if i convert timestamp to unix epoch time ? or doest it also work with timestamp ?

I have tried this kind of query but I don’t have different bars for every product… all products colapse in one bar

Yes, I believe so. If you are not using epoch time, you might be able to use a transformation like this:

I got the same, and it was only when I separated them out as 3 distinct queries did I get the results as 3 separate bars. There may be a transformation or setting somewhere to avoid this, but I could not find it.

I see, well I think it works with my timestamp too , but I have many other values other java,python,c that creating many queries each for every product is unfeasible.

Hi again @alexmondaini

I was able to get it to work using 2 transformations as shown:

BEFORE:

AFTER:

ohh this is amazing, i am away from work laptop now, will try it later… could you explain me the idea behind these 2 transformations ? and why they work ? thanks a lot

Hi @alexmondaini

Were you able to get it working with these 2 transformations? The “Extract fields” transformation parses the fields (in your case, the Product field) and then the “Organize fields” transformation allows us to hide the first one displayed since it was like the original (all 3 products collapsed into one bar).

I played around with this earlier today and found one can do Gantt-style charts using the State timeline. It requires a bit of data manipulation, but as you can see it does work.

Another approach is just to throw a bogus value column

Hi @grant2 , it did not work for me. Introducing a bogus value did work @yosiasz .

The only thing now is that the tooltip shows the bogus value when I hover over the horizontal bars, which is undesirable. I don’t think the tooltip bogus value can be removed right ?

1 Like

In that case 2 options

  1. Remove tool tip as you have enough data to show what each bar value is clearly tooltip would be overkill

  2. Create days left before expiration by calculating obsolete time - today as value then it wont be bogus no more

1 Like