# Month format in perspective table

**URL:** <https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [October 1, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896 "2023-10-01T11:48:39Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Aiasa21](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Aiasa21](https://forum.inductiveautomation.com/u/Aiasa21)\
**Post date:** [October 1, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/1 "2023-10-01T11:48:39Z")

</div>

Good morning, I need your help with a little problem in a perspective table.

I have a query that returns 6 columns. The first one is the month in the numeric type (1, 2, 3, 4 ecc) and this is how is shown in the table. If I change the "render" property of the specific column to "date" I have 01/01/1970 as result.

I wonder how I could trasform the numeric month in the string month ( 1 = January).

Thank you very much for the help

---

<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:** [October 1, 2023, 12:32pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/2 "2023-10-01T12:32:15Z")

</div>

Render as date. Set date format to `MMM` to get "Jan", etc., `MMMM` for "January", etc.

[https://docs.inductiveautomation.com/display/DOC81/Data+Type+Formatting+Reference](https://docs.inductiveautomation.com/display/DOC81/Data+Type+Formatting+Reference)

* * *

Sorry, this is nonsense! You're not returning a date but just the month number. It will need a transform of some sort.  
I'll give a proper solution when i get back to my computer if noone else does in the meantime.

---

<div class="post-metadata">

**Author:** ![Aiasa21](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Aiasa21](https://forum.inductiveautomation.com/u/Aiasa21)\
**Post date:** [October 1, 2023, 12:58pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/3 "2023-10-01T12:58:04Z")

</div>

> [@Transistor](#):
>
> Sorry, this is nonsense! You're not returning a date but just the month number. It will need a transform of some sort.  
> I'll give a proper solution when i get back to my computer if

Yes just the month number because I use a GROUP BY group the months.

I was trying to loop the column of the dataset and transform in some way with the script but I can't figure out how to do.

Thanks for you help!

---

<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:** [October 1, 2023, 1:11pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/4 "2023-10-01T13:11:00Z")

</div>

Post your SQL query and we'll see if we can fix it there.

> [@Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831):
>
> This thread is intended as a short help file for all those struggling to post formatted code on the forum. Posting formatted code on the forum (and most others) is as simple as selecting the code and pressing the \</\> button. It works the same as applying Bold or Italic styles to your document. It preserves indentation and applies syntax highlighting. [How to post code]

---

<div class="post-metadata">

**Author:** ![Aiasa21](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Aiasa21](https://forum.inductiveautomation.com/u/Aiasa21)\
**Post date:** [October 1, 2023, 2:04pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/5 "2023-10-01T14:04:54Z")

</div>

Of course, the column _ **tmp.t\_stamp** _ is the one I'm referencing.

This is the named query so I'm using 2 parameters (year and table name)

```auto

SELECT tmp.t_stamp, Fascia_F1, COALESCE(Fascia_F2, 0) + COALESCE(Fascia_F3, 0) AS Fascia_F2_F3, totalCount, (Fascia_F1 / totalCount * 100) as percentuale_Fascia_F1, ((COALESCE(Fascia_F2, 0) + COALESCE(Fascia_F3, 0)) / totalCount * 100) as percentuale_Fascia_F2_F3
FROM (
	SELECT MONTH(t_stamp) as t_stamp, SUM({Colonna_DB}) AS Fascia_F3
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
			AND ((( HOUR(t_stamp) = 23 OR HOUR(t_stamp) <= 6) AND DAYOFWEEK( t_stamp ) >= 2 AND DAYOFWEEK( t_stamp ) <= 7 )
			OR DAYOFWEEK( t_stamp ) = 1 )
		GROUP BY MONTH( t_stamp )
) as tmp

LEFT JOIN (	SELECT MONTH(t_stamp) as t_stamp, SUM({Colonna_DB}) AS Fascia_F2
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
			AND (( HOUR(t_stamp) = 7 OR ( HOUR(t_stamp) >= 19 AND HOUR(t_stamp) <= 22 ))
			AND DAYOFWEEK( t_stamp ) >= 2 AND DAYOFWEEK( t_stamp ) <= 6 )
			OR (( HOUR(t_stamp) >= 7 AND HOUR(t_stamp) <= 22 )
			AND DAYOFWEEK( t_stamp ) = 7 )					
		GROUP BY MONTH( t_stamp )
		) as tmp2 ON tmp.t_stamp = tmp2.t_stamp
		
LEFT JOIN (	SELECT MONTH(t_stamp) as t_stamp, SUM({Colonna_DB}) AS Fascia_F1
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
				AND HOUR( t_stamp ) >= 08 AND HOUR( t_stamp ) <= 18
				AND DAYOFWEEK( t_stamp ) >= 2 AND DAYOFWEEK( t_stamp ) <= 6	
		GROUP BY MONTH( t_stamp )
		) as tmp3 ON tmp.t_stamp = tmp3.t_stamp
		
LEFT JOIN ( SELECT MONTH(t_stamp) as t_stamp, SUM({Colonna_DB}) AS totalCount
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
		GROUP BY MONTH( t_stamp )
		) as tmp4 ON tmp.t_stamp = tmp4.t_stamp	
		
WHERE totalCount IS NOT NULL	

```

---

<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:** [October 1, 2023, 2:11pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/6 "2023-10-01T14:11:12Z")

</div>

I should have asked, which flavour of SQL? T-SQL / MySQL / etc.?

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [October 1, 2023, 2:12pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/7 "2023-10-01T14:12:10Z")

</div>

This transform worked in my test table:

```auto
#def transform(self, value, quality, timestamp):
	monthColumn = 0 #Change this to the index of your month column
	months = [None, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']
	headers = system.dataset.getColumnHeaders(value)
	data = []
	for row in range(value.rowCount):
		rowData = []
		for column in range(value.columnCount):
			dataPoint = value.getValueAt(row, column)
			if column == monthColumn:
				rowData.append(months[int(dataPoint)])
			else:
				rowData.append(dataPoint)
		data.append(rowData)
	return system.dataset.toDataSet(headers, data)

```

**Original Dataset:**  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/a/7ad458c2dda12a8cbb217b18b805f0d898f176e4.png)

**Result:**  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/4/545db851ded852d48cc97f47654265a990e0fe04.png)

---

<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:** [October 1, 2023, 2:25pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/8 "2023-10-01T14:25:38Z")

</div>

Something like this would return a date of the first of the month. You can then let Ignition do the formatting and, hopefully, get the benefit of localised month names based on the browser location.

**Original**  
`SELECT MONTH(t_stamp) as t_stamp`

**SQL Server**  
`SELECT DATEFROMPARTS(YEAR(t_stamp), MONTH(t_stamp), 1) AS as t_stamp`

**MySQL**

```auto
SELECT STR_TO_DATE(
	CONCAT(YEAR(t_stamp), "-", MONTH(t_stamp), "-01"), '%Y-%M-%d')
) AS t_stamp

```

---

<div class="post-metadata">

**Author:** ![Aiasa21](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Aiasa21](https://forum.inductiveautomation.com/u/Aiasa21)\
**Post date:** [October 1, 2023, 5:47pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/9 "2023-10-01T17:47:57Z")

</div>

Thanks everyone for your help.

@justinedwards.jle, your script works perfectly, I add in the trasform function. Thank you very much!

@Transistor, I use MySQL, I edit the query in the 3 points but I have this error (I removed the last bracket because it seems to be more) ..

_java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gestione\_casa.consumi\_casa.t\_stamp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by_

---

<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:** [October 1, 2023, 6:00pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/10 "2023-10-01T18:00:22Z")

</div>

> [@Aiasa21](#):
>
> ```auto
> 1. SELECT tmp.t_stamp, Fascia_F1, COALESCE(Fascia_F2, 0) + COALESCE(Fascia_F3, 0) AS Fascia_F2_F3, totalCount, (Fascia_F1 / totalCount * 100) as percentuale_Fascia_F1, ((COALESCE(Fascia_F2, 0) + COALESCE(Fascia_F3, 0)) / totalCount * 100) as percentuale_Fascia_F2_F3
> 2. FROM (
> 3. SELECT MONTH(t_stamp) as t_stamp,
> 
> ```

My code should go in line 3.  
Post your new SQL here and someone (with more experience than me) will have a look.

---

<div class="post-metadata">

**Author:** ![Aiasa21](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Aiasa21](https://forum.inductiveautomation.com/u/Aiasa21)\
**Post date:** [October 1, 2023, 6:24pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/11 "2023-10-01T18:24:35Z")

</div>

If I change the code in line 3 as below, it return this error

```auto

SELECT tmp.t_stamp, Fascia_F1, COALESCE(Fascia_F2, 0) + COALESCE(Fascia_F3, 0) AS Fascia_F2_F3, totalCount, (Fascia_F1 / totalCount * 100) as percentuale_Fascia_F1, ((COALESCE(Fascia_F2, 0) + COALESCE(Fascia_F3, 0)) / totalCount * 100) as percentuale_Fascia_F2_F3
FROM (
	SELECT STR_TO_DATE(CONCAT(YEAR(t_stamp), "-", MONTH(t_stamp), "-01"), '%Y-%M-%d') AS t_stamp, SUM({Colonna_DB}) AS Fascia_F3
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
			AND ((( HOUR(t_stamp) = 23 OR HOUR(t_stamp) <= 6) AND DAYOFWEEK( t_stamp ) >= 2 AND DAYOFWEEK( t_stamp ) <= 7 )
			OR DAYOFWEEK( t_stamp ) = 1 )
		GROUP BY MONTH( t_stamp )
) as tmp

LEFT JOIN (	SELECT MONTH(t_stamp) as t_stamp, SUM({Colonna_DB}) AS Fascia_F2
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
			AND (( HOUR(t_stamp) = 7 OR ( HOUR(t_stamp) >= 19 AND HOUR(t_stamp) <= 22 ))
			AND DAYOFWEEK( t_stamp ) >= 2 AND DAYOFWEEK( t_stamp ) <= 6 )
			OR (( HOUR(t_stamp) >= 7 AND HOUR(t_stamp) <= 22 )
			AND DAYOFWEEK( t_stamp ) = 7 )					
		GROUP BY MONTH( t_stamp )
		) as tmp2 ON tmp.t_stamp = tmp2.t_stamp
		
LEFT JOIN (	SELECT MONTH(t_stamp) as t_stamp, SUM({Colonna_DB}) AS Fascia_F1
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
				AND HOUR( t_stamp ) >= 08 AND HOUR( t_stamp ) <= 18
				AND DAYOFWEEK( t_stamp ) >= 2 AND DAYOFWEEK( t_stamp ) <= 6	
		GROUP BY MONTH( t_stamp )
		) as tmp3 ON tmp.t_stamp = tmp3.t_stamp
		
LEFT JOIN ( SELECT MONTH(t_stamp) as t_stamp, SUM({Colonna_DB}) AS totalCount
		FROM consumi_casa
		WHERE YEAR( t_stamp ) = :Anno
		GROUP BY MONTH( t_stamp )
		) as tmp4 ON tmp.t_stamp = tmp4.t_stamp	
		
WHERE totalCount IS NOT NULL

```

_java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gestione\_casa.consumi\_casa.t\_stamp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by_

---

<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:** [October 1, 2023, 6:44pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/12 "2023-10-01T18:44:25Z")

</div>

> [@Aiasa21](#):
>
> ```auto
> STR_TO_DATE(
> CONCAT(YEAR(t_stamp), "-", MONTH(t_stamp), "-01"), '%Y-%M-%d'
> ) 
> AS t_stamp,
> 
> ```

I'm sorry, but I'm not sure what is causing the error. Could it be that your `AS` is naming the modified field as `t_stamp` which is already used by the data table column and used in the `GROUP BY` clause? Can you name it as something else?

---

<div class="post-metadata">

**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:** [October 2, 2023, 8:06am UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/13 "2023-10-02T08:06:19Z")

</div>

What happens if you replace `YEAR(t_stamp)` by `:Anno` in the select ?

---

<div class="post-metadata">

**Author:** ![Aiasa21](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Aiasa21](https://forum.inductiveautomation.com/u/Aiasa21)\
**Post date:** [October 2, 2023, 4:46pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/14 "2023-10-02T16:46:13Z")

</div>

> [@Transistor](#):
>
> what is causing the error. Could it be that your `AS` is naming the modified field as `t_stamp` which is already used by the data table column and used in the `GROUP BY` clause? Can you name it as something else?

I try to change "t\_stamp" in "t\_stamp2" but I have the same error as above

---

<div class="post-metadata">

**Author:** ![Aiasa21](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Aiasa21](https://forum.inductiveautomation.com/u/Aiasa21)\
**Post date:** [October 2, 2023, 4:49pm UTC](https://forum.inductiveautomation.com/t/month-format-in-perspective-table/79896/15 "2023-10-02T16:49:19Z")

</div>

> [@pascal.fragnoud](#):
>
> :Anno

I replace

```auto
STR_TO_DATE(CONCAT(YEAR(t_stamp), "-",

```

in  
`STR_TO_DATE(CONCAT(:Anno, "-",`

but I have the same error as above.

I don't want to drive you crazy, I'm going to use the @justinedwards.jle solution in the script, but thank you very much for your time, you have been very kind.