Open skayliu opened 1 year ago
@skayliu thank you for raising this up. Let's clarify the expectation first.
You need a new built-in function to convert a Unix timestamp into a date-time value. Correct?
Something like:
date and time(1686327521524)
// "2023-06-09T16:18:41Z"
Note that there is an example of how to convert a date-time value into a Unix timestamp here.
We can do the reverse operation with the following expression: (x
- the Unix timestamp)
@"1970-01-01T00:00Z" + @"PT1S" * (x / 1000)
This can be a workaround until there is a new function available.
You need a new built-in function to convert a Unix timestamp into a date-time value. Correct? Something like:
date and time(1686327521524) // "2023-06-09T16:18:41Z"
@saig0, Yes, that's what i want.
@"1970-01-01T00:00Z" + @"PT1S" * (x / 1000)
Thank you, i'll give a try.
@skayliu super. :+1:
I updated the description of the issue and described the new functions to handle Unix timestamps.
I moved the issue to the backlog because I don't plan to work on this issue soon. There is a workaround available.
The issue is open for contributions. :rocket:
@saig0 When with the Z
will raise a incident ,when without the Z
,it can sucessful insert into mysql table.
Here is my test steps.
MySQL table DDL:
CREATE TABLE `business_process_instance` (
`id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`start_time` datetime DEFAULT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Modeler: open this bpmn file in Camunda Modeler
<?xml version="1.0" encoding="UTF-8"?>
<bpmn:definitions xmlns:bpmn="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:dc="http://www.omg.org/spec/DD/20100524/DC" xmlns:zeebe="http://camunda.org/schema/zeebe/1.0" xmlns:di="http://www.omg.org/spec/DD/20100524/DI" xmlns:modeler="http://camunda.org/schema/modeler/1.0" id="Definitions_1oy1tqp" targetNamespace="http://bpmn.io/schema/bpmn" exporter="Camunda Modeler" exporterVersion="5.11.0" modeler:executionPlatform="Camunda Cloud" modeler:executionPlatformVersion="8.2.0">
<bpmn:process id="Process_02287m3" name="Process_02287m3" isExecutable="true">
<bpmn:startEvent id="StartEvent_1">
<bpmn:outgoing>Flow_0msuu6k</bpmn:outgoing>
</bpmn:startEvent>
<bpmn:serviceTask id="Activity_0f5kujc" name="Deal With Timestamp" zeebe:modelerTemplate="com.infosys.camundaconnectors.db.mysql.v1" zeebe:modelerTemplateVersion="1" zeebe:modelerTemplateIcon="data:image/svg+xml;utf8,%3Csvg xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink' width='18' height='18' viewBox='0 0 18 18'%3E%3Cimage xlink:href='data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAKcAAABWCAMAAACdBnZiAAAABGdBTUEAANbY1E9YMgAAAGBQTFRFgrXIQo+s6Ojp1OLn7KlSF3WZ8saM55ctysvN9teu7rZrtLW319jZ+eTHmZuewNrj8Pb4pcrXYKG54O3xMISk+fn5cKrA8/Pzpqiqn6GkrK6w/fbtv8HD5Y4aAGiP////mhPjgAAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAZsSURBVGje1Zppg3M6FIATxFLETlUr//9f3pPFLoaO17j5oCI1np49xyB21UCkbUkafXv3ZZitHCS6Nydp86QknBRnd+ZsWy7IJP9SpJdx5m0pPhMi1Y9RdFP7JFLfcd52plrG9+OMwUAVVoIQwkKsBN2Ok0Vtm47nZspJc/NunDwyJVMRl5wU3Y2TpZ2J9iPDPE7FN+MEE8XSQHHvQSJLmffi5CYqBJqRwVRN7v7JvTj7IArAg5TTPaDXckIQlScYT+z2Z9BrOc22lbZomvMaJbkTJ1Qj5dplHqCiO3H2il+ErB+8/mLOrFP8fEAgzeP7cAJPqout6ybxR5yJCqFrsXXLRK/mhBCKtemfxPfhnJdN41iwofnLOXkMSrSaz+7DCa6EdQt6gf4BZ65TPAiU3Icz1ufIXL90PWeyIbRWZxJ/4kfpRiGtSVd/wJlvlEalNmhdz9m28Vb614SmyznNUSm/Wjeld8lHZLueWhf3H3BitS3SCRTdSp543bPBLEh2kzpE1XfpkeR5OWcsDZDoqrhofeH6uER4/Mz0naV1gSJmiFF89UzDrcRn4B4pl0qhdqIXaIuTJSeVw/2G06WG+PTpwS0nZHJo1iNdUuKd0XjO6cNH9aJcMqtyNYxKnVXGeAqfI85ir0pEvQThJ0K6fUaClz1cxSmeGNQgV78o6AsgaM0YPzOecPFpwAXXh692U3Hy9HtOV6jEpQHMnvUmMrQXYyAxY6KtSGL5vsFccBY+rQzqVyyA6ZtySXFwGhT1qwKmujAofRtmPy1qgDXrnvNdFS514Sr/ids2BHyE8OiUaKsjnpnQxNNG9vkWqoejCZPXix9oEVAXvAzkZNA3d5huGgjJDXrnEnw+xQWfVj/ujWWhmbb5ViWQj3q4wPlS/i6dgT/p+axo8IbDG6Yvn49ASqmfSsKpH8GxAp2IH7QNmiuAdLvrjQbdd/bZP5SLxqVvWphwMJkp5Aa/Q3L2U1NM3z0niBBsgl/5UZxTW93sKkVgIsmcs6qfgfHmz68ov1hTMDb2ql3DBQJldd1UntCe8xWYPvwsdesB0Dz+IYrJshoxv7f6yq/pS8zePjw/8LnsiveTPt2CGWLWT+Gkfr4DX3K6PlC+TIlsHNsupRsWGkVRKlV/dt6suKzPAM0Q7t7b4fM5fVobR+9JyvUsryDTJD6f89tCYW0r3+Iy+st+yN7+42RfeldO8RIETTjjpMQYo3i9JMA4RQf+AaEJF2NYnFxuZvc0s+zKNZ9HA6d6cd8F1MV3+Sj3QYYP57McCuZhzRcsWxpzKGb2an3XvWBEGelf22dap9vxYg8i6uOzOgSlvbrkeBucUoK5pEJJz7KoskZLeAenBlNwWpq1j7fByeLhLS1CA8y8ZYIPcYZKRNZ8wJr3WS6qS80GpySV+UhySkNEi40K38bs5LT1T1PilFruvcfqbtjg5KTixZLilComi8gA7Mc4m9U1Z42kcfZwQpYnAKo4o3ThLplEj49xrq9JFYdrUv6ZE1o8Zc8plZzPwwJEpIET8THzfBPzYf5TTvDonlM5TTTp7QrXGjjXJBv1t9mrLOdwsoEzmVGgLlQd43z8a05GpqFJTs39nGGXZLoxyphnciaTDDmIdy/nWpy3vPM5pUF2LYq89//dnMxeye5OeDansshkHOPZIU7WeMtCRICeyhmPYj0emA9wToo3T4n3cTanSkDREOPZN5zzjO+czpm1Q0NlSPffc7K+XjqXU2k762J8/A84PVksebs5w4dl2TPOSMV2NC5HT5anPdSdezi9zyP0HKuZcMpoROJJyP8tpzOrl1Qtas/rpdVM1jj2x3Jsx55yJqNitAMaJn2K2sfZyMBvDfVnOAZ9PDr4ZhDxQpwO+zDb8qwpJyNDCR8tOFXpj9FoDDHCntfxzqeXovcZJ9VJ4gqHTLbYCoTwR9nHcbzQmXGi5U5jNBttRKYj0m+BLLa5P3oMFfNyhCDP5tN8Qnsuz5jMxTmxSkSOctrN2AYWycqe7EGWnMx5WAyynDfjhCJ/vhGeSDdOcL6f03o023t7qw+pILJVzhC8yALbQVnEx6gZwv+7fdwBEevmRh+1q+s2GiHLdkgzcS1NL6URNYPNdfLb/hLaiks7tvtOuPM5v+Qsv+bsvNy7gFNtU9vsq7u9A6BHOLVhCbN/Ps7gzOP/Aye5pNX7O05o7ybskvEfhvpHKSG5AmkAAAAASUVORK5CYII=' x='0' y='0' height='100%25' width='100%25'/%3E%3C/svg%3E">
<bpmn:extensionElements>
<zeebe:taskDefinition type="com.infosys.camundaconnectors.db:mysql:1" />
<zeebe:ioMapping>
<zeebe:input source="mysql.insert-data" target="operation" />
<zeebe:input source="127.0.0.1" target="databaseConnection.host" />
<zeebe:input source="3306" target="databaseConnection.port" />
<zeebe:input source="root" target="databaseConnection.username" />
<zeebe:input source="zeebe123" target="databaseConnection.password" />
<zeebe:input source="exampledb" target="data.databaseName" />
<zeebe:input source="business_process_instance" target="data.tableName" />
<zeebe:input source="=[{ "id": processInstance.id, "start_time": @"1970-01-01T00:00" + @"PT1S" * (processInstance.createTime / 1000) + @"PT28800S" }]" target="data.dataToInsert" />
</zeebe:ioMapping>
<zeebe:taskHeaders>
<zeebe:header key="resultVariable" value="dealStatus" />
</zeebe:taskHeaders>
</bpmn:extensionElements>
<bpmn:incoming>Flow_0msuu6k</bpmn:incoming>
<bpmn:outgoing>Flow_1ys2snz</bpmn:outgoing>
</bpmn:serviceTask>
<bpmn:sequenceFlow id="Flow_0msuu6k" sourceRef="StartEvent_1" targetRef="Activity_0f5kujc" />
<bpmn:endEvent id="Event_1jic5xr">
<bpmn:incoming>Flow_1ys2snz</bpmn:incoming>
</bpmn:endEvent>
<bpmn:sequenceFlow id="Flow_1ys2snz" sourceRef="Activity_0f5kujc" targetRef="Event_1jic5xr" />
</bpmn:process>
<bpmndi:BPMNDiagram id="BPMNDiagram_1">
<bpmndi:BPMNPlane id="BPMNPlane_1" bpmnElement="Process_02287m3">
<bpmndi:BPMNShape id="_BPMNShape_StartEvent_2" bpmnElement="StartEvent_1">
<dc:Bounds x="179" y="99" width="36" height="36" />
</bpmndi:BPMNShape>
<bpmndi:BPMNShape id="Activity_0s9c7x9_di" bpmnElement="Activity_0f5kujc">
<dc:Bounds x="330" y="77" width="100" height="80" />
<bpmndi:BPMNLabel />
</bpmndi:BPMNShape>
<bpmndi:BPMNShape id="Event_1jic5xr_di" bpmnElement="Event_1jic5xr">
<dc:Bounds x="552" y="99" width="36" height="36" />
</bpmndi:BPMNShape>
<bpmndi:BPMNEdge id="Flow_0msuu6k_di" bpmnElement="Flow_0msuu6k">
<di:waypoint x="215" y="117" />
<di:waypoint x="330" y="117" />
</bpmndi:BPMNEdge>
<bpmndi:BPMNEdge id="Flow_1ys2snz_di" bpmnElement="Flow_1ys2snz">
<di:waypoint x="430" y="117" />
<di:waypoint x="552" y="117" />
</bpmndi:BPMNEdge>
</bpmndi:BPMNPlane>
</bpmndi:BPMNDiagram>
</bpmn:definitions>
dataToInsert: The key point here, when with the Z
will raise a incident ,when without the Z
,it can sucessful insert into mysql table.
Before:
[{
"id": processInstance.id,
"start_time": @"1970-01-01T00:00Z" + @"PT1S" * (processInstance.createTime / 1000)
}]
Start current diagram with this payload:
{
"processInstance":{
"id":"71182b87-dae4-473a-b378-0907accd9f93",
"controlState":"active",
"createTime":1686637501241
}
}
Variables: In Zeebe-Play we can see variables like this.
processInstance {"id":"71182b87-dae4-473a-b378-0907accd9f87","controlState":"active","createTime":1686637501241}
data {"dataToInsert":[{"start_time":"2023-06-13T06:25:01Z","id":"71182b87-dae4-473a-b378-0907accd9f87"}],"tableName":"business_process_instance","databaseName":"exampledb"}
operation "mysql.insert-data"
Incidents: And a incident raised.
Unable to insert data into the table: Data truncation: Incorrect datetime value: '2023-06-13T06:25:01Z' for column 'start_time' at row 1
After:
dataToInsert: remove the Z
and deploy the model again.
[{
"id": processInstance.id,
"start_time": @"1970-01-01T00:00" + @"PT1S" * (processInstance.createTime / 1000)
}]
Start current diagram with this payload:
{
"processInstance":{
"id":"71182b87-dae4-473a-b378-0907accd9f95",
"controlState":"active",
"createTime":1686637501241
}
}
Variables: In Zeebe-Play we can see variables like this and the record inserted successfully
processInstance {"id":"71182b87-dae4-473a-b378-0907accd9f95","controlState":"active","createTime":1686637501241}
data {"dataToInsert":[{"start_time":"2023-06-13T06:25:01","id":"71182b87-dae4-473a-b378-0907accd9f95"}],"tableName":"business_process_instance","databaseName":"exampledb"}
operation "mysql.insert-data"
dealStatus {"response":"1 row(s) inserted successfully"}
Query mysql table:
SELECT id,start_time FROM business_process_instance WHERE id ='71182b87-dae4-473a-b378-0907accd9f95'
id |start_time |
------------------------------------+-------------------+
71182b87-dae4-473a-b378-0907accd9f95|2023-06-13 06:25:01|
Is your feature request related to a problem? Please describe.
When using the mysql connector (related: zeebe-play#196), I got a timestamp value of
1686327521524
or1686327521
, I want to get the datetime of2023-06-10 00:18:41
or2023-06-10T00:18:41
so that I can insert the datetime into mysql table.Describe the solution you'd like New built-in functions to handle Unix timestamps.
Signatures:
Examples:
Related issues