# SQL Parse Timestamp for Chart

**URL:** <https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [January 18, 2024, 5:09pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461 "2024-01-18T17:09:16Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Luke\_Waller](https://avatars.discourse-cdn.com/v4/letter/l/46a35a/32.png) [@Luke\_Waller](https://forum.inductiveautomation.com/u/Luke_Waller)\
**Post date:** [January 18, 2024, 5:09pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/1 "2024-01-18T17:09:16Z")

</div>

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

---

<div class="post-metadata">

**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:** [January 18, 2024, 5:21pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/2 "2024-01-18T17:21:09Z")

</div>

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

```auto
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](https://docs.inductiveautomation.com/display/DOC81/Named+Queries)

> **[SQL GROUP BY Statement](https://www.w3schools.com/sql/sql_groupby.asp)**
>
> W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

---

<div class="post-metadata">

**Author:** ![Luke\_Waller](https://avatars.discourse-cdn.com/v4/letter/l/46a35a/32.png) [@Luke\_Waller](https://forum.inductiveautomation.com/u/Luke_Waller)\
**Post date:** [January 18, 2024, 5:21pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/3 "2024-01-18T17:21:58Z")

</div>

Thank you, in work now.

---

<div class="post-metadata">

**Author:** ![Luke\_Waller](https://avatars.discourse-cdn.com/v4/letter/l/46a35a/32.png) [@Luke\_Waller](https://forum.inductiveautomation.com/u/Luke_Waller)\
**Post date:** [January 23, 2024, 7:25pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/4 "2024-01-23T19:25:20Z")

</div>

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?

---

<div class="post-metadata">

**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:** [January 23, 2024, 8:14pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/5 "2024-01-23T20:14:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Luke\_Waller](https://avatars.discourse-cdn.com/v4/letter/l/46a35a/32.png) [@Luke\_Waller](https://forum.inductiveautomation.com/u/Luke_Waller)\
**Post date:** [January 23, 2024, 8:17pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/6 "2024-01-23T20:17:48Z")

</div>

```auto
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")

```

---

<div class="post-metadata">

**Author:** ![Luke\_Waller](https://avatars.discourse-cdn.com/v4/letter/l/46a35a/32.png) [@Luke\_Waller](https://forum.inductiveautomation.com/u/Luke_Waller)\
**Post date:** [January 23, 2024, 8:30pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/7 "2024-01-23T20:30:46Z")

</div>

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?

---

<div class="post-metadata">

**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:** [January 23, 2024, 9:46pm UTC](https://forum.inductiveautomation.com/t/sql-parse-timestamp-for-chart/84461/8 "2024-01-23T21:46:58Z")

</div>

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.

```auto
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.