Hi,
I'm trying to display historized data on a line chart but running into issues with getting the values to show correctly.
In the screenshot below, you can see the data being returned from the database:
My goal is to trend these values on the chart, but so far I haven't been successful. Below is the current chart configuration:
I tried converting the datetime to UnixTimestamp, and it kinda works — the trends now display values, but the timestamps are shown in Unix format instead of a readable datetime format
[
{
"id": "eb7baf9594b86c97",
"type": "ui-form",
"z": "temp_historian_flow",
"name": "Date-Time Picker",
"group": "2baf9c9d8f909ab3",
"label": "",
"order": 2,
"width": 0,
"height": 0,
"options": [
{
"label": "Start Date",
"key": "Start Date",
"type": "date",
"required": true,
"rows": null
},
{
"label": "End Date",
"key": "End Date",
"type": "date",
"required": true,
"rows": null
},
{
"label": "Start Time",
"key": "Start Time",
"type": "time",
"required": false,
"rows": null
},
{
"label": "End Time",
"key": "End Time",
"type": "time",
"required": false,
"rows": null
}
],
"formValue": {
"Start Date": "",
"End Date": "",
"Start Time": "",
"End Time": ""
},
"payload": "",
"submit": "Generate",
"cancel": "Clear",
"resetOnSubmit": false,
"topic": "topic",
"topicType": "msg",
"splitLayout": true,
"className": "",
"passthru": false,
"dropdownOptions": [],
"x": 470,
"y": 540,
"wires": [
[
"a4d97ad20723270a"
]
]
},
{
"id": "a4d97ad20723270a",
"type": "function",
"z": "temp_historian_flow",
"name": "Temp Data Return",
"func": "\nfunction combineDateTime(date, time) {\n return `${date} ${time}:00`;\n}\n\nlet startDateTime = combineDateTime(msg.payload['Start Date'], msg.payload['Start Time']);\nlet endDateTime = combineDateTime(msg.payload['End Date'], msg.payload['End Time']);\nlet selectedTags = flow.get('tagSelect');\n\n// Ensure selectedTags is always an array\nif (!Array.isArray(selectedTags)) {\n selectedTags = selectedTags ? [selectedTags] : [1];\n}\n\n// Handle empty array case\nif (selectedTags.length === 0) {\n selectedTags = [1];\n}\n\n// Get the selected table from flow context\nvar selectedTable = 'historian.temperature_history';\n\n// Build dynamic WHERE clause for multiple tags\nlet tagPlaceholders = selectedTags.map(() => '?').join(',');\nlet whereClause = `tag_name IN (${tagPlaceholders})`;\n\n// Build the SQL query to handle multiple tags\nmsg.topic = `SELECT tag_name, temperature_value as tempValue, DATE_FORMAT(Timestamp, '%Y-%m-%d %H:%i:%s') as datestamp \n FROM ${selectedTable} \n WHERE Timestamp BETWEEN ? AND ? AND ${whereClause}\n ORDER BY tag_name, Timestamp`;\n\n// Prepare parameters array: [startDateTime, endDateTime, ...selectedTags]\nmsg.payload = [startDateTime, endDateTime, ...selectedTags];\n\nreturn msg;\n\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 730,
"y": 540,
"wires": [
[
"1165ed5841c50f6e"
]
]
},
{
"id": "1165ed5841c50f6e",
"type": "mysql",
"z": "temp_historian_flow",
"mydb": "mysql_config",
"name": "Industrial History DB",
"x": 980,
"y": 540,
"wires": [
[
"bd53d45c3b4a499a"
]
]
},
{
"id": "bd53d45c3b4a499a",
"type": "ui-chart",
"z": "temp_historian_flow",
"group": "2baf9c9d8f909ab3",
"name": "",
"label": "chart",
"order": 3,
"chartType": "line",
"category": "tag_name",
"categoryType": "property",
"xAxisLabel": "Timestamp",
"xAxisProperty": "datestamp",
"xAxisPropertyType": "property",
"xAxisType": "linear",
"xAxisFormat": "",
"xAxisFormatType": "auto",
"xmin": "",
"xmax": "",
"yAxisLabel": "F",
"yAxisProperty": "tempValue",
"yAxisPropertyType": "property",
"ymin": "0",
"ymax": "100",
"bins": 10,
"action": "replace",
"stackSeries": false,
"pointShape": "circle",
"pointRadius": 4,
"showLegend": true,
"removeOlder": 1,
"removeOlderUnit": "3600",
"removeOlderPoints": "",
"colors": [
"#0095ff",
"#ff0000",
"#ff7f0e",
"#2ca02c",
"#a347e1",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"textColor": [
"#666666"
],
"textColorDefault": true,
"gridColor": [
"#e5e5e5"
],
"gridColorDefault": true,
"width": 6,
"height": 8,
"className": "",
"interpolation": "linear",
"x": 1270,
"y": 540,
"wires": [
[]
]
},
{
"id": "d8f8b9173cc97d59",
"type": "ui-button",
"z": "temp_historian_flow",
"group": "2baf9c9d8f909ab3",
"name": "",
"label": "Clear",
"order": 0,
"width": 0,
"height": 0,
"emulateClick": false,
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"iconPosition": "left",
"payload": "[]",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"buttonColor": "",
"textColor": "",
"iconColor": "",
"enableClick": true,
"enablePointerdown": false,
"pointerdownPayload": "",
"pointerdownPayloadType": "str",
"enablePointerup": false,
"pointerupPayload": "",
"pointerupPayloadType": "str",
"x": 1270,
"y": 600,
"wires": [
[
"bd53d45c3b4a499a"
]
]
},
{
"id": "2baf9c9d8f909ab3",
"type": "ui-group",
"name": "Group 15",
"page": "25086a72528c822c",
"width": 6,
"height": 1,
"order": 3,
"showTitle": true,
"className": "",
"visible": "true",
"disabled": "false",
"groupType": "default"
},
{
"id": "mysql_config",
"type": "MySQLdatabase",
"name": "Industrial History Database",
"host": "localhost",
"port": "3306",
"db": "historian",
"tz": "local",
"charset": "UTF8"
},
{
"id": "25086a72528c822c",
"type": "ui-page",
"name": "Temperature Trends",
"ui": "ed79b03d683e2b34",
"path": "/page7",
"icon": "home",
"layout": "grid",
"theme": "6d8bff5f3fded5c2",
"breakpoints": [
{
"name": "Default",
"px": "0",
"cols": "3"
},
{
"name": "Tablet",
"px": "576",
"cols": "6"
},
{
"name": "Small Desktop",
"px": "768",
"cols": "9"
},
{
"name": "Desktop",
"px": "1024",
"cols": "12"
}
],
"order": 7,
"className": "",
"visible": true,
"disabled": false
},
{
"id": "ed79b03d683e2b34",
"type": "ui-base",
"name": "Siroflex Demo",
"path": "/dashboard",
"appIcon": "",
"includeClientData": true,
"acceptsClientConfig": [
"ui-notification",
"ui-control"
],
"showPathInSidebar": false,
"headerContent": "dashboard",
"navigationStyle": "icon",
"titleBarStyle": "default",
"showReconnectNotification": true,
"notificationDisplayTime": 1,
"showDisconnectNotification": true
},
{
"id": "6d8bff5f3fded5c2",
"type": "ui-theme",
"name": "FF Theme",
"colors": {
"surface": "#1f2937",
"primary": "#8ce2e7",
"bgPage": "#4b5563",
"groupBg": "#ffffff",
"groupOutline": "#cccccc"
}
}
]
I'd appreciate any suggestions on how to configure the chart to display this data. Thank you all.
