SQL Parse Timestamp for Chart

Ignition
1

I am trying to create a chart that extracts the amount of operational hours from the database organized by month. Basically cell utilization chart. I have 'timestamp' data in the database as well as "Cycle Time" for each operation formatted in minutes. I have tried several methods. So far this is the only part of the code I can get to work:
SELECT CAST (Timestamp AS DATE) FROM PartData_Log

My best guess is to generate cases for every month then sum the "Cycle Time" by those cases but I have been unable to make that work

2

It sounds as though you want a SUM function which will require use of the GROUP BY operation.

SELECT
    CAST(Timestamp AS DATE) AS Date,
    SUM(Minutes) AS CumulativeMinutes
FROM PartData_Log
WHERE 
    TimeStamp >= :startDate
    AND
    TimeStamp < :endDate
GROUP BY CAST(Timestamp AS DATE)

Try this out in the Named Query editor and create variables for the :startDate and :endDate parameters.

(I haven't tested this query.)

https://docs.inductiveautomation.com/display/DOC81/Named+Queries

3

Thank you, in work now.

4

I was able to use this syntax:

SELECT DATENAME (MONTH,("Timestamp")) ,SUM ("Cycle Time")/3600,MAX ("Timestamp")

FROM PartData_Log
Where
("Timestamp") >= DATEADD (MONTH,-11,DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1))
Group By
DATENAME (MONTH,("Timestamp"))
order by
MAX ("Timestamp")

Now my problem is that I have three column's of data output and I only want to use the first two. It is messing up my chart. How do I suppress one of the column outputs in my chart?

5

Hi, Luke. Can you fix the code formatting in your post. Hit the pencil icon to edit, select the SQL code and hit the </> button.

6 
SELECT DATENAME (MONTH,("Timestamp")) ,SUM ("Cycle Time")/3600,MAX ("Timestamp")

FROM PartData_Log
Where 
	("Timestamp") >= DATEADD (MONTH,-11,DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1))
Group By
	DATENAME (MONTH,("Timestamp"))
order by
	MAX ("Timestamp")
7

THanks for looking at it. I basically want to turn off one column of data produced by the query "timestamp". We are using the timestamp for another column but I don't need the data directly. I can remove the column in ignition and it works but only in testing. Once I save it, it reverts to include all three, column's of data
Maybe there is another way to get ignition to ignore this column?

8

You might add which chart component is using this data. I haven't used Vision for quite a while so you may need someone more familiar with it but there should be a way of selecting which columns to display..

Tips:

  • Be consistent in your upper/lowercase for SQL keywords. You've got some in uppercase and some in proper case and some in lowercase.
  • Add a space after every comma, as in standard English.
  • "Timestamp" shouldn't need ( ) brackets other than for the MAX function.
  • Don't be afraid to break lines for legibility.
SELECT 
    DATENAME(MONTH, "Timestamp"),
    SUM("Cycle Time") / 3600,
    MAX("Timestamp")
FROM PartData_Log
WHERE
    Timestamp >= DATEADD(
        MONTH, 
        -11, 
        DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    )
GROUP BY
DATENAME(MONTH, "Timestamp")
ORDER BY MAX("Timestamp")

When we make your SQL readable we can see that you have asked for three columns in the SELECT statement.