Remap timestamps without rounding

Hello ,

I have a question regarding the REMAP_TIMESTAMPS function.
How can I avoid, that the result of the REMAP_TIMESTAMPS Code below is rounded:

REMAP_TIMESTAMPS ( HOUR_NOW ( ) , HOURS , THURSDAY TUESDAY MONDAY FRIDAY WEDNESDAY )

Many thanks in advance for the support.

Best regards,
Jonas

Hi Jonas,

as HOUR_NOW() is already “rounded” to the hour (no minutes, seconds, milliseconds) the rounding in REMAP_TIMESTAMPS does not affect you here anyway.
However, in general, you can “avoid” the rounding by choosing a smaller time unit and divide the result by the corresponding factor.
Example:
REMAP_TIMESTAMPS ( HOUR_NOW ( ) , HOURS , THURSDAY TUESDAY MONDAY FRIDAY WEDNESDAY ) / 24
gives you the number of days since 01.01.1970, as a decimal with HOURS precision.

Best
David

Hi David,
many thanks for your reply. I had the same idea, but urgently it does not work.

.
image .
Do you have any idea what else might be causing it?

Thanks and best regards,
Jonas

Hi Jonas,

which Celonis version are you using? If it’s an older on-prem version you might need to convert the REMAP_TIMESTAMPS result into a FLOAT first by multiplying it with *1.0 .

Let me know if that helps.

Best
David

Hi David,
we are using the version below:
image

Unfortunately this still does not work:


image

Best regards
Jonas

Hi Jonas,

okay, in this version, it is not required anymore to multiply with 1.0, as divisions always return a FLOAT.
Then the reason is that REMAP_TIMESTAMPS ignores times when you specify days that should be taken into account. So in this case, it only counts full days, so any number returned by REMAP_TIMESTAMPS is a multiple of 24.
In order to take times into account, you can add the time range 00:00-23:59 to at least one of the specified days.
Example:
REMAP_TIMESTAMPS(HOUR_NOW(), HOURS, MONDAY 00:00-23:59 TUESDAY WEDNESDAY THURSDAY FRIDAY) / 24

This behavior is documented here under “Day based calendar”.

Cheers
David

Hi David,
many thanks for your support. It seems to be working now.

Best regards
Jonas