Business Intelligence Tools / Data Science & Analytics / Developers Corner

TERR — Converting strings to date and time

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 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.

Script convert strings to Date or Time with TERR


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!



4 thoughts on “TERR — Converting strings to date and time

  1. Nan Cheng Reply

    I was trying to use aggregate() to apply min function to a column (which is factor type but formatted as date) and group by ID. e.g. aggregate(df$date, by= list(df$ID), FUN = min)… I first use strptime(as.character(df$date)) to make it of class POSIXct, but I couldn’t work aggregate() out because the result turns out to some numbers instead of dates. This function works fine in R studio but doesn’t work in TERR. Any way to work around this?


    • Nan Cheng Reply

      Well, I found a way around to perform aggregate() on date type myself. Just let me know if you are interested so that I post it here.

  2. Cássia Carvalho Reply


    could share which transformation you used to corvert a string date Filed into a date type?

    Thanks in advance.

    • Julie Sebby Post authorReply

      I used a calculate column transformation I think to create a new column. You could also use calculate and replace.

Leave a Reply

Your email address will not be published. Required fields are marked *