How can I convert a text having a timestamp format to a timestamp?
Hello there,
Please is there any function on explore that could allow me to convert a text in this format "2022-11-21T15:19:18" to a timestamp.. Indeed I need this in order to catch the first reply time on a ticket when an outbound call happened (generating automatically a private note that the normal first reply time metric can't catch).. Our technology team could implement a trigger that catches that exat timestamp when the outbound call took place but that trigger could be only stored in a text field.. so now the issue is on how to convert that text to a timestamp and then calculating the difference between it and the ticket created timestamp ..
Thanks!!
-
Hi,
Yoi can convert it first to Date with:
DATE(_year,_month,_day,_hour,_minute,_second)
And then convert it to Timestamp with:
DATE_TO_TIMESTAMP(_date)
To get the "year", "month", "day", "hour", "minute" and "seconds" you can play with the text functions FIND (to find index to chars '-' or 'T' or ':') , RIGHTPART and LEFTPART to trim the text.
Details on text functions:
-
Hi,
Thank you for your suggestion!
I tried it but it gave me an empty result.. any ideas why?
-
Hi,
Your formulas indexes are wrong. Using SUBSTR with example date "2022-11-21T15:19:18":
SUBSTR(DS Outbound call time), 0,4) with this one you get "2022" (year)
SUBSTR(DS Outbound call time), 5,7) with this one you get "11" (month)
SUBSTR(DS Outbound call time), 8,10) with this one you get "21" (day)
SUBSTR(DS Outbound call time), 11,13) with this one you get "15" (hour)
SUBSTR(DS Outbound call time), 14,16) with this one you get "19" (minute)
SUBSTR(DS Outbound call time), 17,19) with this one you get "18" (second)
-
Hi,
Oh yes sorry..but even when correcting the indexes, the result is still empty.. I guess the issue is with the date() function because when testing the substr() functions seperately they all worked!
-
Hello,
So I could convert the text field into a timestalp using the following function
And I could compute the first reply time for those cases when we have and outbound call happening : DATE_DIFF([DS Outbound Call Timestamp],[Ticket created - Timestamp],"nb_of_seconds")/60/60 ..
Now what I want to do is to compare the regular First reply time (we have already integrated in explore) and the one I built per each ticket and take always the smallest value because that's the real value we're trying to catch.. I've been trying several functions and calculations with calculated attributes and metrics using the attribute_fix, etc and nothing gave me the desired result .. any ideas please on how I can do that?
Thanks!
-
Convert the substrings to be numeric:
DATE(
NUMBER(SUBSTR([DS Outbound call time],0,4)),
NUMBER(SUBSTR([DS Outbound call time],5,7)),
NUMBER(SUBSTR([DS Outbound call time],8,10)),
NUMBER(SUBSTR([DS Outbound call time],11,13)),
NUMBER(SUBSTR([DS Outbound call time],14,16)),
NUMBER(SUBSTR([DS Outbound call time],17,19))
)
Iniciar sesión para dejar un comentario.
6 Comentarios