Convert DateTime to integer

[Treki]

Hello,

we have strings in the format "2020-11-10T12:43:58" saved in Kibana.

When using ES SQL, we want to convert these strings into an integer or float so that the integer/float increases as the DateTime increases as well.

For example:
"2020-01-01T00:00:00" → 1
"2020-01-01T00:01:00" → 2
"2020-01-03T00:01:00" → 100

We want to add this new column to our SQL search result.

How can we do this?
Can someone show us an example, please?

With best regards

[AClerk]

What is the logic for numbering?
What is business use?
When you say ESQL, that is in Canvas workpad?
Please provide some more details.

[Treki]

We have significant issues ordering our data table in the Canvas workpad accordingly to the timestamps.

I explain the full problem here: Link
If this problem is solved then we are happy and we can continue our work.

In order to make the data table only present elements which lie between two dates, we thought to take these steps:

1. Adjust the ES SQL code so that the preview of the result is what we would like to get
2. We add an additional column so that timestamp1 and timestamp2 (timestamp1 < timestamp2) are getting numbers which are number1<number2
3. In the expression editor, we set 'filterColumn' to the column, which we created at step 2

We hope that you understand our request.
We just want to filter our data table according to a given timespan.

With best regards