brunosso
Posts: 4
Joined: Tue Jan 04, 2022 8:06 pm

Create charts from MySQL DB

Hi!

i'm new to this framework and i'm in trouble with plotting my two sensor.

I have a MySQL db populated with this data:
Image

the field value1 and value2 are float
the field reading_time is timestamp

my php landing page is this https://pastebin.com/NUVcA0yF

i'm in trouble because i think the xAxislabel is not correct (infact the dateTimeLabelFormats don't work). There is some procedure to import or parse the timestamp from the MySQL data?

Thanks
mateusz.b
Posts: 2006
Joined: Tue Jul 13, 2021 11:34 am

Re: Create charts from MySQL DB

Hi brunosso,

Welcome to our forum and thanks for contacting us with your question!

It doesn't work the way you would expect, because you use categories, and even though you set type: 'datetime', the actual axsis type is category. As you can read in our API docs, dateTimeLabelFormats works only for datetime axes. Here is what you can do:

- keep using categories, but use formatter callback along with dateFormat method to format your labels:
https://jsfiddle.net/BlackLabel/124zt6ea/

- parse your data and use datetime axis, remember that Highcharts expect date to be in UTC timestamp format:
https://jsfiddle.net/BlackLabel/u4e7objx/

Let me know if you have any further questions!
Regards!
Mateusz Bernacik
Highcharts Developer
brunosso
Posts: 4
Joined: Tue Jan 04, 2022 8:06 pm

Re: Create charts from MySQL DB

Thanks for the suggestion!

With the first example works like a charm!

I want to ask in case i want to use a Range Selector i need to parse the column from the DB so Highcharts use the data correctly?
or i can use the range selector keep using categories?

Thanks
brunosso
Posts: 4
Joined: Tue Jan 04, 2022 8:06 pm

Re: Create charts from MySQL DB

I'm making progress!!!

I edit the MySQL db to save readings time into UNIX timestamp (with a trigger before insert)
Now i have this source data
Image

now i can follow your second example to manage correctly the datetime axis
mateusz.b
Posts: 2006
Joined: Tue Jul 13, 2021 11:34 am

Re: Create charts from MySQL DB

Hi brunosso,

To use native Highcharts range selector you need to have X axis of type datetime. However, it is possible to keep using category axis and create a custom range selector which would apply range with setExtremes() method. That method is used by native range selector under the hood.
Demo: https://jsfiddle.net/BlackLabel/wr1sb27c/

Feel free to ask any further questions!
Regards!
Mateusz Bernacik
Highcharts Developer
brunosso
Posts: 4
Joined: Tue Jan 04, 2022 8:06 pm

Re: Create charts from MySQL DB

Thanks for your suggestion!

I find the solution in a very simple way!
I'm using now the zoomType options in the chart section. Now i can zoom a portion of the plot and after reset the zoom to full data set!
For my needs is perfect!

Now little graphical tweaks and i'm ready.

Last question (i hope) is possible to localize the name of month or days? I'm italian and i want to use Gennaio instead of January.

Thanks
mateusz.b
Posts: 2006
Joined: Tue Jul 13, 2021 11:34 am

Re: Create charts from MySQL DB

Hi brunosso,

Please take a look at this topic. You will learn there how to set language options for dates. You can also refer directly to our docs: https://api.highcharts.com/highcharts/lang

If you would like to change lang options dynamicaly, based on user location, you need to find the user localization youself, because Highcharts can't do that.

Let me know if that was what you were looking for!
Regards!
Mateusz Bernacik
Highcharts Developer

Return to “Highcharts Usage”