Access current datetime in Table analytic?

1

Is there a way to access the current datetime stamp in a table analytic?

I’m trying to create an aging analytic and I know when the record was created but I have no way (that I can tell) to calculate the duration delta between now and then.

1 Like
2

To close the loop on this, the answer is no.

It took me the better part of yesterday but I figured out how to use the Tulip Node Red API nodes and perform the calculation outside of the platform.

Here’s the flow I ended up with:

image
image1267×417 157 KB

If there is interest I can try to document it in detail but here’s the key points:

If your query will return more than 100 records, you need to use flow variables to keep track of where you are.

  1. Setting the initial values:
    image

  2. Write them to the flow variables. NOTE: The includeTotalCount is a more expensive query on the server but it’s required so you can track how many times to loop over the records.

    image
    image537×588 14.2 KB

  3. The split node is needed because we need to update the records one at a time.

  4. Because I decided to use an Interval Tulip data type I need the result in seconds. Since Node Red uses microseconds the results need to be divided by 1000.
    image

  5. The record count returns as a string so convert it to a number:
    image

  6. I ended up using a negative condition to know when to stop looping. Basically when the number of records remaining is less than the query limit, stop.
    NOTE: The record count is relative to the offset so it will actually decrease by 100 every time you loop.
    image

  7. Only continue looping if the response is 200 which means everything is good.
    image

  8. Calculate the offset increment and save back to the offset flow variable.
    image

  9. Use the flow variables to set the message inputs for the next query:

    image
    image537×592 14.5 KB

  10. Make sure to change the Output as from plain text to Parsed JSON or it will not be accepted by the Tulip Record Update node.

I could keep going but hopefully this is enough to save you half a days work!

3

Hey @Richard-SNN, a late follow up to this: Release r288 will include a CURRENTDATEANDTIME() function in the analytics expression editor.

Here is a quick demo with a few examples for how to use it.

2 Likes