How can I convert a text having a timestamp format to a timestamp?

5 Kommentare

  • Preventium Health - Websays

    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:

    https://support.zendesk.com/hc/en-us/articles/4408834558746-Explore-functions-reference#topic_l1t_plf_dhb

     

     

    0
  • Sahar Akrout

    Hi, 

    Thank you for your suggestion!

    I tried it but it gave me an empty result.. any ideas why? 

    0
  • Preventium Health - Websays

    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)

     

     

    0
  • Sahar Akrout

    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!

    0
  • Sahar Akrout

    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!

    0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.

Powered by Zendesk