Display Data From Named Query (MySQL) On a Time series Chart

Ignition
1

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
image1180×908 44 KB

This is a perspective project , any advise or solution ?

2

The time series chart requires timestamps in the first column of its datasets. You are providing integers. Perhaps you need the XY Chart.

3

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.
4

If I Use XY chart< it says "No series properly configured"

5

Yes, you'll have to configure it. You need to tell it which columns to use, etc.

6

To replace the Hours column, do I need to do that on the DB?

7

You need to decide if you are using my option 1 (Time Series) or 2 (XY Chart).

8

Id like to try 1 1st

9

Post your SQL query. See Wiki - how to post code on this forum.

10 
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)
11

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,

SELECT
    FLOOR(t_stamp / 3600000) * 3600000 AS Hour

This will preserve the t_stamp as a DateTime type.

12

Where in the Query should i add this?

13

It replaces the first line of your query.

14

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:

dateadd(hour, datediff(hour, 0, t_stamp), 0)

However, in very recent versions of SQL Server, you can use:

datetrunc(hour, t_stamp)

Note the slightly different syntax.

Edit: Shoot. MySQL, not MS SQL. :man_facepalming:

Probably this:

timestampadd(hour, timestampdiff(hour, '1970-01-01', t_stamp), '1970-01-01')
15

image
image925×831 32.5 KB

This is what i get :frowning:

16

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.

17 
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

18

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.)

19

image
image1022×842 45 KB

Get this, would just like the x axis to show in an hourly formatt now

20

timeAxis.tick.label.format : hh