Скачать или смотреть Converting Decimal Seconds to Time of Day in Tibco Spotfire

Learn how to convert decimal seconds into the corresponding time of day in Tibco Spotfire, giving you accurate data representation.
---
This video is based on the question https://stackoverflow.com/q/68033057/ asked by the user 'MegaLoMonkey' ( https://stackoverflow.com/u/5927255/ ) and on the answer https://stackoverflow.com/a/68033678/ provided by the user 'Monte_fisto' ( https://stackoverflow.com/u/6280672/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Tibco Spotfire - time in seconds & milliseconds in Real, convert to a time of day

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Converting Decimal Seconds to Time of Day in Tibco Spotfire

If you work with data in Tibco Spotfire, you may find yourself needing to convert a list of times represented in decimal format of seconds into a specific time of day. This problem arises frequently in data analysis and visualization tasks where time-based data is essential. In this post, we will explore a straightforward solution to converting decimal seconds into a time of day, applying the relevant offsets based on a given start time.

The Problem at Hand

Imagine you are working with a dataset that includes a start date and time alongside a series of elapsed times listed as decimals. For instance, you might have a time series that began on 26th May 2021 at 11:58 AM. The elapsed time is expressed in seconds and might look something like this:

0.00000 seconds

0.10100 seconds

0.20200 seconds

and so on...

Your goal is to convert these decimal seconds into formatted time values, taking into consideration the start time of 11:58 AM.

The Initial Attempt

You might start with a formula that helps to convert seconds into a TimeSpan like this:

[[See Video to Reveal this Text or Code Snippet]]

While this formula appears to work initially, it essentially ignores the milliseconds because it uses the decimal portion of the time incorrectly. The goal is to modify this approach to include milliseconds in our final calculated time.

A Correct Approach to Conversion

Step 1: Adjust the Formula

To include both whole seconds and milliseconds in your time calculation, you'll need to convert the seconds correctly. A refined formula could look like this:

[[See Video to Reveal this Text or Code Snippet]]

Here’s how it works:

TimeSpan() takes four parameters: hours, minutes, seconds, and milliseconds.

Integer(Floor([Time])) captures the whole seconds from your decimal time.

([Time] - Integer(Floor([Time]))) * 1000 retrieves the fractional seconds and converts them into milliseconds for accuracy.

Step 2: Adding It to the Start Time

Once you have the TimeSpan, the next step is to add this duration to your specified start time. You can achieve this using the DateAdd function:

[[See Video to Reveal this Text or Code Snippet]]

This effectively combines your starting date and time with the calculated TimeSpan, resulting in an accurate time of day for each entry.

Step 3: Formatting the Output

For the final representation, set the column format in Spotfire so that your time of day displays properly. A recommended format is:

[[See Video to Reveal this Text or Code Snippet]]

This format will show the complete date along with hours, minutes, seconds, and milliseconds, providing clarity and precision in your time series data.

Conclusion

Converting decimal seconds to a proper time of day format can seem daunting at first, but with the steps laid out here, you can achieve accurate outcomes in Tibco Spotfire. By correctly manipulating the time components and effectively utilizing the TimeSpan and DateAdd functions, you can generate precisely integrated time stamps for your datasets.

Now that you know how to convert decimal seconds into time of day while accounting for milliseconds, you can apply this knowledge to enhance your data analysis effectively. Happy analyzing!