# Display Data From Named Query (MySQL) On a Time series Chart **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [May 6, 2024, 11:05am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955 "2024-05-06T11:05:07Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 11:05am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/1 "2024-05-06T11:05:07Z")
Hi All, Please assist urgently, I have created a name query and a table that shows different parts produced per hour. I would like to visualize this on a time series chart, I am not wining. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/4/84641b749bd79f875c74132b4ab94dd0d500c032.png) This is a perspective project , any advise or solution ? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 6, 2024, 11:13am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/2 "2024-05-06T11:13:52Z")
The time series chart requires timestamps in the first column of its datasets. You are providing integers. Perhaps you need the XY Chart. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [May 6, 2024, 11:14am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/3 "2024-05-06T11:14:45Z")
The Time Series chart component expects a proper DateTime column for the X-axis. You haven't got one. You have two options: 1. Replace the "Hour" column data with 6 → `2024-05-06 06:00:00`. 2. Use the XY Chart component instead. ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 11:38am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/4 "2024-05-06T11:38:54Z")
If I Use XY chart\< it says "No series properly configured" ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [May 6, 2024, 11:41am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/5 "2024-05-06T11:41:58Z")
Yes, you'll have to configure it. You need to tell it which columns to use, etc. > **[Perspective - XY Chart | Ignition User Manual](https://docs.inductiveautomation.com/docs/8.1/appendix/components/perspective-components/perspective-chart-palette/perspective-xy-chart)** > > The XY Chart displays data trends. ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 11:43am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/6 "2024-05-06T11:43:34Z")
To replace the Hours column, do I need to do that on the DB? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [May 6, 2024, 11:45am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/7 "2024-05-06T11:45:34Z")
You need to decide if you are using my option 1 (Time Series) or 2 (XY Chart). ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 11:59am UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/8 "2024-05-06T11:59:37Z")
Id like to try 1 1st ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [May 6, 2024, 12:03pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/9 "2024-05-06T12:03:56Z")
Post your SQL query. See [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 12:05pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/10 "2024-05-06T12:05:19Z")
```python SELECT Hour(t_stamp) AS Hour, Sum(CASE WHEN ScannedString LIKE '2640%' THEN 1 ELSE 0 END) AS Total_Units_2640, Sum(CASE WHEN ScannedString LIKE '2764%' THEN 1 ELSE 0 END) AS Total_Units_2764, Sum(CASE WHEN ScannedString LIKE '2640%' OR ScannedString LIKE '2764%' THEN 1 ELSE 0 END) AS Total_Units_Both FROM mes_live.hvac_finished_goods_rack_sample WHERE t_stamp >= Concat(CurDate(), ' 06:00:00') AND t_stamp < Concat(CurDate(), ' 22:00:00') AND Packed_Flag = 0 GROUP BY Hour(t_stamp) ``` ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [May 6, 2024, 12:16pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/11 "2024-05-06T12:16:05Z")
> [@Joachim\_Govender](#): > > `SELECT Hour(t_stamp) AS Hour` `t_stamp` is in milliseconds since 1970-01-01 00:00:00. There are 60 × 60 × 1000 = 3,600,000 ms in one hour. For MySQL tou can round to the hour with something like, ```python SELECT FLOOR(t_stamp / 3600000) * 3600000 AS Hour ``` This will preserve the t\_stamp as a DateTime type. ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 12:18pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/12 "2024-05-06T12:18:00Z")
Where in the Query should i add this? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [May 6, 2024, 12:18pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/13 "2024-05-06T12:18:37Z")
It replaces the first line of your query. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 6, 2024, 12:20pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/14 "2024-05-06T12:20:10Z")
Instead using `hour(t_stamp)`, you need to _truncate_ your `t_stamp` to the start of the hour. The technique varies by DB brand. In PostgreSQL, there's a function explicitly for this: `date_trunc(t_stamp, 'hour')`. In MS SQL Server, the recommended method for performance is this: ```python dateadd(hour, datediff(hour, 0, t_stamp), 0) ``` However, in very recent versions of SQL Server, you can use: ```python datetrunc(hour, t_stamp) ``` Note the slightly different syntax. Edit: Shoot. **My** SQL, not **MS** SQL. 🤦‍♂️ Probably this: ```python timestampadd(hour, timestampdiff(hour, '1970-01-01', t_stamp), '1970-01-01') ``` ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 12:22pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/15 "2024-05-06T12:22:31Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/c/4c4c50d275b2a43a6702448bc3b2e149761de5fd.png) This is what i get ☹ ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [May 6, 2024, 12:31pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/16 "2024-05-06T12:31:17Z")
All your timestamps are the same, you boinked something up somewhere. Show use what you did so we can spot the error, and tell us what database you're using. ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 12:48pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/17 "2024-05-06T12:48:26Z")
```python SELECT DATE_FORMAT(t_stamp, '%Y-%m-%d %H:00:00') AS Hour, SUM(CASE WHEN ScannedString LIKE '2640%' THEN 1 ELSE 0 END) AS Total_Units_2640, SUM(CASE WHEN ScannedString LIKE '2764%' THEN 1 ELSE 0 END) AS Total_Units_2764, SUM(CASE WHEN ScannedString LIKE '2640%' OR ScannedString LIKE '2764%' THEN 1 ELSE 0 END) AS Total_Units_Both FROM mes_live.hvac_finished_goods_rack_sample WHERE t_stamp >= CONCAT(CURDATE(), ' 06:00:00') AND t_stamp < CONCAT(CURDATE(), ' 22:00:00') AND Packed_Flag = 0 GROUP BY DATE_FORMAT(t_stamp, '%Y-%m-%d %H:00:00'); ``` I did this ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 6, 2024, 12:51pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/18 "2024-05-06T12:51:04Z")
Did it work? `date_format()` yields a string (that looks like a date), not an actual date/time or timestamp. (Converting timestamps to/from strings is a great deal of extra overhead, both in the DB and in Ignition. Avoid it.) ---
**Author:** ![Joachim\_Govender](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@Joachim\_Govender](https://forum.inductiveautomation.com/u/Joachim_Govender)\ **Post date:** [May 6, 2024, 12:53pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/19 "2024-05-06T12:53:40Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/1/f1d76b087eb951dcd74d86c2c62268edb33e78ca.png) Get this, would just like the x axis to show in an hourly formatt now ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [May 6, 2024, 1:00pm UTC](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955/20 "2024-05-06T13:00:13Z")
`timeAxis.tick.label.format : hh` [Next page](https://forum.inductiveautomation.com/t/display-data-from-named-query-mysql-on-a-time-series-chart/89955.md?page=2)