How do I convert days to milliseconds and add that to a UNIX timestamp

I currently have a dataset with the shipping_time in a Double format column (e.g. 5.007) and would like to know how to convert that into milliseconds to add that to a UNIX timestamp.

I’ve actually currently managed to do it with 2 math formula nodes, the first using the formula for converting Days to milliseconds and the second for adding the milliseconds to the UNIX timestamp, but I would like to know if there is a better way of doing it.

Hi @Leongwd, there’s a Date&Time Shift node that may be of use here, but as to whether that reduces or increases the complexity over what you’ve already done, I’m not sure without experimenting. My thoughts are that would most likely just complicated things.

From the sound of what you’ve done, which I’m assuming is something like multiplying days by 86400000 and then adding that to the timestamp, I’d imagine that is likely to be about as simple as it gets.

3 Likes

Hi @takbb, thanks for your suggestion, I’ve just tried using the Date&Time Shift node but unfortunately, it doesn’t seem to be accepting UNIX timestamps as its input, it only accepts those that are in Date&Time format.

I can convert the UNIX timestamp to a Date&Time format and then convert it back to UNIX but I think that would be more complicated than using the 2 math formula nodes.

2 Likes

@Leongwd in KNIME often you would just use a sequence of nodes to get to a certain result and there is no shame in that :slight_smile:

With UNIX timestamps you will always have to be careful to be sure to deal with seconds milliseconds or even nanoseconds and thingy might get more complicated once you start transferring the data to other systems (databases, Excel, …).

Within KNIME it might be best to stick to the KNIME format of date and time. Outside of KNIME or in transition sometimes it might be best to keep the date/time as a string or readable format and maybe convert it back later. In every case you might want to carefully check the results and pay special attention once you deal with timezones.

You might want to share an example of what you are looking for without spelling some secrets.

5 Likes

@mlauber71 Thank you for your suggestions, I’ll try to keep that in mind when designing workflows in the future

1 Like

There is only one node then can do all, one node “to rule them all” :sweat_smile:

3 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.