New function to deal with timestamp values

[skayliu]

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 or 1686327521, I want to get the datetime of 2023-06-10 00:18:41 or 2023-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:

to unix timestamp(from: date and time): number

from unix timestamp(from: number): date and time

Examples:

to unix timestamp(@"2023-06-09T16:18:41Z")
// 1686327521524

from unix timestamp(1686327521524)
// "2023-06-09T16:18:41Z"

Related issues

• Camunda Autormation Platform 7:
• Zeebe broker:

[saig0]

@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.

[skayliu]

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.

[saig0]

@skayliu super. 👍

I updated the description of the issue and described the new functions to handle Unix timestamps.

[saig0]

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. 🚀

[skayliu]

@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=&#39;http://www.w3.org/2000/svg&#39; xmlns:xlink=&#39;http://www.w3.org/1999/xlink&#39; width=&#39;18&#39; height=&#39;18&#39; viewBox=&#39;0 0 18 18&#39;%3E%3Cimage xlink:href=&#39;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=&#39; x=&#39;0&#39; y=&#39;0&#39; height=&#39;100%25&#39; width=&#39;100%25&#39;/%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="=[{  &#10;  &#34;id&#34;: processInstance.id,&#10;  &#34;start_time&#34;: @&#34;1970-01-01T00:00&#34; + @&#34;PT1S&#34; * (processInstance.createTime / 1000) + @&#34;PT28800S&#34;&#10;}]" 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|

[saig0]

@skayliu thank you for sharing. 👍

It seems that the datetime in MySQL doesn't contain a timezone.