time difference across dates in a column



  • Konsta Danyliuk

    Hello Anson.
    As far as I understand, you would like to add a new column that will represent the difference in days between the previous and the current creation_date_string value, wouldn't you?

    There is the TIMESTAMPDIFF function that returns the number of whole date intervals between two date arguments. It could be used to calculate the difference in days between two dates.

    However before applying this function you have to prepare your dataset. I would go with the following approach.

    • Ensure that the initial data is sorted by creation_date_string column.
    • At the initial Sheet, create a new column (let's call it Identifier) and add the same value to each record. You could just use any integer or a string.
    • At the new Sheet, apply GROUPBY(#InitialData!Identifier) to get all records in a single group.
    • Then add GROUP_PREVIOUS(#InitialData!creation_date_string). This will return previous creation_date_string value for each record.
    • Then reference #InitialData!creation_date_string and the next column. 
    • Add MIDNIGHT function to both creation_date_string and previous_creation_date_string columns to round the timestamps up to midnight. As far as I understand, you only care about the day when the new data has been generated not the exact time it happened, don't you?
    • Introduce TIMESTAMPDIFF("DAY";#previous_creation_date_string;#creation_date_string) to calculate the difference between the new data portions in days.
    • Reference any other columns you need from the InitialData.

    I hope this helps. Do not hesitate to let me know if you have any further questions.



    Comment actions Permalink
  • Anson Chan

    Hello Konsta, this is great! Thank you very much for your kind advice. I followed your guidance and am able to produce the difference in days between the times

    Kind regards,


    Comment actions Permalink
  • Konsta Danyliuk

    You are welcome, Anson.

    Comment actions Permalink

Please sign in to leave a comment.