This post explains my struggle to convert strings to Date or Time with TERR. I recently spent so much time on this that I thought it deserved a blog post. Here’s the story…
I was recently working on a TERR data function that calls a publicly available API and brings all the data into a table. I used the as.data.frame function to parse out my row data. In that function, I used the stringsAsFactors = FALSE argument, and as a result (the desired result), all of my data came back as strings. This was fine because the API included column metadata with the data type. As you can see in the script below, I planned on “sapplying” through the metadata with as.POSIXct and as.numeric. This worked just fine in RStudio, and it also worked for the numeric columns and for the DateTime columns. However, it did not work for Date and Time columns. I tried different syntax, functions (as.Date didn’t work either), packages, etc to get it to work and NOTHING! The struggle was very real.
Finally, I Googled the right terms and came across a TIBCO knowledge base article with this information….
Spotfire data functions recognize TERR objects of class “POSIXct” as date/time information. As designed, the Spotfire/TERR data function interface for date/time information does the following:
– Converts a Spotfire value or column whose DataType is “Date”, “Time” or “DateTime” into a TERR object of class “POSIXct”.
– Converts a TERR object of class “POSIXct” into a Spotfire value or column with a DataType of “DateTime”, which can then be formatted in Spotfire to display only the date (or to display only the time) if needed.
This interface does not use any other TERR object classes (such as the “Date” class in TERR) to transfer date/time information between Spotfire and TERR.
That told me that all my effort was for naught, and it just wasn’t possible. I contacted TIBCO just to make sure there wasn’t some other solution out there that the article was not addressing. In the end, I just used a transformation on the Date and Time columns to change the data type. I hope that you, dear Reader, find this post before you spend hours on the same small problem. I did put in an enhancement request. Fingers crossed. Please let me know if you have a better method!
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!