FromDate , ToDate in Gantt Chart

General Discussion
, , ,
1

Hi
i had one tag historian with machine status, timestamp is back to back, which means the first timestamp is fromDate for Machine Status and toDate time Stamp is the timeStamp for the next row.

 [{"t_stamp":1636027386514,"MachineStatus":"Own Fault"},  
{"t_stamp":1636027447697,"MachineStatus":"Downstream Fault"},
{"t_stamp":1636027450786,"MachineStatus":"UpStream"},
{"t_stamp":1636027494988,"MachineStatus":"Idle"},
{"t_stamp":1636027496031,"MachineStatus":"Own Fault"},
{"t_stamp":1636027497048,"MachineStatus":"Production Mode"}]

i am looking for to convert this history Dataset above to the below dataset which combined timestamp fromDate>Todate as per the below formate to be used in GanttChart.

[{"fromDate":1636027386514,"toDate":1636027447697,"MachineStatus":"Own Fault"},
{"t_stamp":1636027447697,"toDate":1636027450786,"MachineStatus":"Downstream Fault"},
{"t_stamp":1636027450786,"toDate":1636027494988,"MachineStatus":"UpStream"},
{"t_stamp":1636027494988,"toDate":1636027386514,"MachineStatus":"Idle"},
{"t_stamp":1636027496031,"toDate":1636027496031,"MachineStatus":"Own Fault"},
{"t_stamp":1636027497048,"toDate":1636027497048,"MachineStatus":"Production Mode"}]
2

Look at your database’s “LEAD()” windowing function.

1 Like
3

Thanks a lot @pturmel. i able to do it in SQL using LEAD() Function as mentioned by you.
i am looking to do it by python Script as i am using direct Ignition TagHistorian to retrieve Dataset .

4 
dataIn =  [
           {"t_stamp":1636027386514,"MachineStatus":"Own Fault"},  
           {"t_stamp":1636027447697,"MachineStatus":"Downstream Fault"},
           {"t_stamp":1636027450786,"MachineStatus":"UpStream"},
           {"t_stamp":1636027494988,"MachineStatus":"Idle"},
           {"t_stamp":1636027496031,"MachineStatus":"Own Fault"},
           {"t_stamp":1636027497048,"MachineStatus":"Production Mode"}
          ]
           
dataOut = []

for row, nextRow in zip(dataIn, dataIn[1:]):
	dataOut.append(
	               {
	                'fromDate': row['t_stamp'], 
	                'toDate'  : nextRow['t_stamp'],
	                'MachineStatus' : row['MachineStatus']
	               }
	              )

dataOut.append(
               {
                'fromDate': nextRow['t_stamp'], 
	            'toDate'  : nextRow['t_stamp'],
	            'MachineStatus' : nextRow['MachineStatus']
               }
              )
3 Likes
5

thanks for the solution. the last set of data fromData and to data are the same which gave an error in the Gantt chart .to make it work i have to delete the second append but i will lose the last set of data .

actually, data came from historian and based on date range from >> to . from/to DateTime can be inside the t_stamp which whill get the next row as the first or last row not full cover within from/to

for example :
row1 event(a) @ 10 :00:00 AM
row 2 event (b) @ 10:35:00 AM
row 3 event (c) @ 10:40:00 AM

so, if I need to get the duration for even (a) between ( 10:20 am to 11 Am) I will get null.