DATE_DIFF() with a custom date and custom time fields

Hey Ned,

Your approach to calculating the time difference between two timestamps in Zendesk Explore is conceptually correct, but there seems to be a small issue with the SUBSTR function usage, which might be causing the incorrect results. Let's address the potential issue and correct the formula.

Understanding SUBSTR in Your Context

The SUBSTR function extracts a substring from a string, starting at a specified position. The positions are usually indexed starting from 1 rather than 0. This might be the root of the problem in your formula.

Corrected Formula

Given your description, if your Start Time and End Time fields are in the "HH:MM" format, you should adjust the SUBSTR function to start at position 1 for hours and position 4 for minutes, considering the indexing starts at 1. Also, ensure you're using the correct field types ([Start Date - Timestamp] vs [Start Date - Date], as mentioned differently in your examples). You'll want to use the Date fields correctly converted to timestamps if necessary.

Let's correct the formula based on the assumption you're working with Date fields and need to add time parts to them:

DATE_DIFF(
    DATE_ADD(
        [Start Date - Date], "minute", 
        NUMBER(SUBSTR([Start Time (HH:MM)], 1, 2)) * 60 + NUMBER(SUBSTR([Start Time (HH:MM)], 4, 2))
    ),
    DATE_ADD(
        [End Date - Date], "minute", 
        NUMBER(SUBSTR([End Time (HH:MM)], 1, 2)) * 60 + NUMBER(SUBSTR([End Time (HH:MM)], 4, 2))
    ), 
    "minute"
)

I haven't tested this in my instance, but hopefully it points you down the right path.

Brandon

Hi Brandon,

Thank you for looking into this!

From the "Explore functions reference" article the SUBSTR function example is using the 0 index for the first character and the end index is always - 1. The SUBSTR function seems to be fine as I'm visualizing the result under "Calculated Start Date" and "Calculated End Date" and the timestamps are as expected.

I have tried using "Date" and "Timestamp" of the date object with the same result.

I think the timestamps are calculated correctly but the DATE_DIFF function is not liking them. I've tried the function with hard-coded timestamps and I'm getting mixed results. Here are some examples:

DATE_DIFF("2024-02-28T06:50:00", "2024-02-28T08:55:00", "nb_of_minutes")

Returns: -125

DATE_DIFF("2024-02-28T06:50:00", "2024-02-28T08:55:00", "minute")

Returns: -5

DATE_DIFF("2024-02-27T08:55:00", "2024-02-28T08:55:00", "nb_of_minutes")

Retunrs: -1440

DATE_DIFF("2024-02-27T08:55:00", "2024-02-28T08:55:00", "minute")

Returns: 0

It's not very clear what the "_date" value format is from the "Explore functions reference" article and the examples are covering only dates without time.

Interesting - I'm going to reach out to the Community for additional evaluation here.  More to come!

I figured it out after some trial and error. I converted the returned value from DATE_ADD and DATE_DIFF is calculating the time correctly:

DATE_DIFF(
    DATE(
        DATE_ADD([Start Date - Timestamp], "minute", 
            NUMBER(SUBSTR([Start Time (HH:MM)], 0, 2)) * 60 + NUMBER(SUBSTR([Start Time (HH:MM)], 3, 5))
                )
        ),
    DATE(
        DATE_ADD([End Date - Timestamp], "minute", 
            NUMBER(SUBSTR([End Time (HH:MM)], 0, 2)) * 60 + NUMBER(SUBSTR([End Time (HH:MM)], 3, 5))
                )
        ),
        "nb_of_minutes"
)

Hi Ned, 

Good job on the self-solve!  

I did some testing with your initial formula vs the modified one (that works) in my test account and I too noticed the same results.  

Here's what I think happened here

Initial Formula Issue

In your initial formula, you directly added minutes to a timestamp and then attempted to calculate the difference between these modified timestamps. However, the problem likely arises from how the DATE_ADD function interacts with the timestamp fields and how the DATE_DIFF function interprets these results. If the DATE_ADD function doesn't return a value in the exact format expected by DATE_DIFF, or if there's any precision loss in the date-time representation, it can lead to incorrect calculations.

Corrected Formula Explanation

Your corrected formula works because it explicitly converts the result of DATE_ADD back into a date format using the DATE function before calculating the difference. This step ensures that the inputs to DATE_DIFF are in a consistent and compatible format, eliminating any ambiguity or formatting issues that could lead to incorrect calculations. Essentially, it standardizes the date-time values, ensuring that the DATE_DIFF function can accurately calculate the difference in minutes.

Hi David,

Thank you for the details!

Unfortunately it's very hard to debug the formulas and the article for the functions is missing details about the expected data types and returned type.

Yeah it's tough to troubleshoot these formulas and it really just comes down to trial and error.  I'll pass that info along to our documentation team to see if we can get more in-depth with these Explore articles.  

Olá,

Estou enfrentando um problema com a seguinte fórmula:

IF 

  DATE_DIFF(

    [Atribuição do ticket - Carimbo de data/hora], 

    DATE_ADD(

      [Criação do ticket - Carimbo de data/hora], "hour",

      VALUE(Tempo da primeira resposta (h))

    ), 

    "nb_of_second"

  ) < 0

  THEN NULL

ELSE 

  DATE_DIFF(

    [Atribuição do ticket - Carimbo de data/hora],

    DATE_ADD([Criação do ticket - Carimbo de data/hora], "hour",

    VALUE(Tempo da primeira resposta (h))

  ),

  "nb_of_second"

  )

ENDIF

O objetivo: Retornar o tempo ocioso entre a última atribuição do ticket a um agente e o momento da primeira resposta. Caso o resultado seja menor que zero, a fórmula deve retornar nulo, pois entendo que a primeira resposta não foi realizada pelo último agente atribuído, tornando o dado desnecessário.

O problema: Ao rodar esse código, recebo a seguinte mensagem de erro: “Ocorreu um erro. Entre em contato com o Suporte ao cliente Zendesk (código de erro 7).”

Solicito o apoio da equipe para identificar a causa desse erro.