DataMeer TIMESTAMPDIFF Rounding issue?

Follow
Elwyn Roberts

To calculate the number of seconds between two dates, I am using the TIMESTAMPDIFF function (only if there is a start datetime).

IF(ISBLANK(#Start), 0, TIMESTAMPDIFF("SECOND", #Start, #End))

For some reason it is not reporting the correct number of seconds.  I have highlighted the easiest example in that the difference is clearly 41 seconds.  But the function I am using returns 40 seconds.  Some, not all, are similar.... the first row should be 141 seconds.  The seconds row of 233 is fine.

Is there anything that can be done to fix this, or is this a known issue with the TIMESTAMPDIFF function and what would the alternative be?

Many thanks in advance.

0

Comments

7 comments

Date Votes
  • Konsta Danyliuk

    Hello Elwyn.
    I hope you are doing well.

    I've just tried reproducing this behavior in my lab but can't.
    What Dataneer version do you have? Would you see the difference of 40 or 41 seconds if you create the following logic?

    • Col #A - ASDATE("16-01-2023 10:30:52"; "dd-MM-yyyy HH:mm:ss")
    • Col #B - ASDATE("16-01-2023 10:31:33"; "dd-MM-yyyy HH:mm:ss")
    • Col #C - TIMESTAMPDIFF("SECOND";#A;#B)
    • Col #D - IF(ISBLANK(#C);0;TIMESTAMPDIFF("SECOND";#A;#B))

    By the way, do you want time to convert this thread to a support ticket? I guess it will be easier to provide timely assistance this way.

    1
    Comment actions Permalink
  • Elwyn Roberts

    Currently using Version 11.1.12

    A ticket wont be necessary as what you suggested did work.  But I am confused as to why.  The field #Start and #End were already DATE type fields so I used FORMATDATE instead.  eg

    ASDATE(FORMATDATE(#Start ; "dd-MM-yyyy HH:mm:ss"))

    Seems odd that I have to take a date field (with no issue with the time etc), turn it to string and back to a date field in order to get it to work but it does.

    Thanks for the assistance..
      

    0
    Comment actions Permalink
  • Alan

    Hi Elwyn,

    Datameer DATE fields store data as Epoch time, the number of milliseconds since UNIX Epoch.  I suspect we may be encountering some incorrect rounding based on the internal representation.  Converting a DATE that does not present the Millisecond data into to a STRING and then back to DATE would strip all the millisecond data off.  

    Could you please create two new fields on this sheet for your Start and End columns that presents the column data as Milliseconds?
    TIMESTAMP(#Start)
    TIMESTAMP(#End)

    Please provide the millisecond output for the highlighted date.  That will give us the real data to use for internal testing.  

    0
    Comment actions Permalink
  • Elwyn Roberts
    • Edited

    Here you are.  I also included the fix mentioned from Konsta and I did the TIMESTAMP for this as well, just for reference - I guess to show what values are going into TIMESTAMPDIFF that works and what doesnt work.



    0
    Comment actions Permalink
  • Alan

    Hi Elwyn,

    Thank you for supplying this collateral.  We'll perform some testing an let you know what the outcome is.

    Alan

    0
    Comment actions Permalink
  • Konsta Danyliuk
    • Edited

    Hello Elwyn.

    Here is what is going on under the hood.

    • Datameer stores a DATE as a number of milliseconds from the epoch.
    • By default, Datameer displays a date in the format MMM DD, YYYY HH:MM:SS without milliseconds. That's why 1673883095000 and 1673883095999 are displayed as Jan 16, 2023 04:31:35 PM (CEST).

    • There is a Format Cells feature that allows you to change the date appearance, e.g., one could display milliseconds.


    • When it comes to math operations over dates, Datameer works with a number of milliseconds and then displays the result as a date.
    • The TIMESTAMPDIFF function displays the difference in the whole numbers of appropriate intervals, but it is still the number of milliseconds multiplied by the corresponding coefficient, e.g., 1000 for SECOND. If the difference between the two numbers is less than 1000, the TIMESTAMPDIFF("SECOND";#A;#B) returns 0.


    • This is precisely where the confusion comes from. The difference between 1673883095500 and 1673883096001 is 501 milliseconds, but the former value is 04:31:35 PM and the latter 04:31:36 PM. 


    How could one deal with this?

    • Depends on where a date is coming from, it could have or not have milliseconds.
    • All dates converted from strings have ms set to 0, if not only the ms is a part of the parse pattern.

    Possible approaches

    1. Convert the date into a string and then back into a date with the ASDATE function to round ms to 0.
    2. Subtract milliseconds from a date via #DateColumn - MILLISECOND(#DateColumn).
    3. Use the TIMESTAMPDIFF function with the MILLISECOND interval and convert results to, e.g., seconds using chosen rules to round the values.

    I hope the above clarification makes sense. Do not hesitate to let us know if you have any further questions.

    0
    Comment actions Permalink
  • Elwyn Roberts

    Very detailed explanation - thanks.  

    As mentioned, I am using your first solution (formatting to drop the milliseconds) which solves the issue.  

    Thanks to both of you for the follow up and the solution.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post