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
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
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.
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!
Post your SQL query and we'll see if we can fix it there.
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)
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
I should have asked, which flavour of SQL? T-SQL / MySQL / etc.?
This transform worked in my test table:
#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:
Result:
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
SELECT STR_TO_DATE(
CONCAT(YEAR(t_stamp), "-", MONTH(t_stamp), "-01"), '%Y-%M-%d')
) AS t_stamp
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
My code should go in line 3.
Post your new SQL here and someone (with more experience than me) will have a look.
If I change the code in line 3 as below, it return this error
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
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?
What happens if you replace YEAR(t_stamp) by :Anno in the select ?
I try to change "t_stamp" in "t_stamp2" but I have the same error as above
I replace
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.