Converting Integer to Date

Product Suggestions
,
1

Hi Tulip Community,

I feel like I’m missing something simple here, as I’d imagine this is a common need in variable-type conversions:

A connector function I run returns an Object list which I am displaying in an interactive table. One of the variable types of this object list is Text and another is Date/Time. My goal is to find a certain searchstring in the table and return the date.

Using MAP_TO_TEXT_LIST and MAP_TO_NUMBER_LIST, to create arrays, and ARRAY_INDEX_OF to find the index of the searchstring then ARRAY_VALUE_AT_INDEX of the number list (date list) to get the proper date in Unix timestamp (number format).

How do I convert this Unix timestamp to a Tulip Date/Time variable? I can’t find a function in the expression editor to handle this.

2

Is the issue when setting the connector output to date/time, that there is no map-to-list function for date formats?

3

I mean that would solve the problem too.

But Tulip is allowing me to store the date column as a number in a number list, which is find by me for this use case. I just need a way to convert that number (which is in Unix format) back to date.

4

As a workaround, you can make a SQL connector and build this function:

image
image2017×412 26 KB

(if your format is in seconds but not milliseconds, just remove the “/ 1000”)

You can then use this connector function as a converter.

I don’t know if there is any easier way to solve this natively in the expression editor.

EDIT:

Here you go:

image
image1979×361 35.2 KB

Make sure, you calculate second or milliseconds, whatever you have available!

5

Thanks Thorsten, I’ll use this work around of adding to the Unix origin date. Hoping to possibly see an added function in the future for this. Thanks for your help!

6

@thorsten.langner thanks for the great help here!

@iamwout - I changed this to a product suggestion so we have the request for a native function in expression editor to make this easier logged in our system :slight_smile:

7

@thorsten.langner @iamwout Actually was just told of another way to do this!

There is a functionality in the expression editor for this, albeit a bit strange texttodatetime(totext(:miliseconds), ‘x') or texttodatetime(totext(:seconds), ‘X')
Moment uses x and X for time since epoch; Moment.js cheatsheet

1 Like