Time difference

[ashis]

Hi

In our data base we have date field as  below , Year-month-day hours:min:sec:nano sec

2018-03-23 22:32:24.043

When I am pulling the date field in the load script from the DB , it is reading as "DD/MM/YYYY hh:mm:ss:TT"

1) My first question is why it is discarding the nano second

2) I want to calculate end date and start date in terms of second so I am using the following formula:

=Interval((EndTime) - (StartTime),'ss')

But for some cases it is not showing right value.

for example difference between end and start time is showing 00 , however it should be 1

Kindly give your input.

[tresesco]

Could you share your sample qvw?

[Anonymous]

Hi,

SET TimestampFormat='m/dd/yyyy h:mm:ss TT';

LOAD * ,

interval(endTime-startTime) as Diff

INLINE [

    startTime, endTime

    4/24/2018 5:02:53 AM, 4/24/2018 5:02:54 AM

];

[jonathandienst]

>>DD/MM/YYYY hh:mm:ss:TT

This is incorrect. TT is the AM/PM indicator so it has converted the time to a 12 hour clock and you are rounding the display to the nearest second. You need format string 'DD/MM/YYYY hh:mm:ss.fff'.

>> difference between end and start time is showing 00 , however it should be 1

Not necessarily. Your format has hidden but not removed the second fractions, so the difference might be less than 0.5 seconds, which will round down to 0.

If the field EndTime and StartTime are loaded as strings, then you need to convert with

TimeStamp#(EndTime, 'yyyy-MM-dd hh:mm:ss.fff')

[ashis]

Hi Jonathan,

It seems my starttime and endtime is loading as text , do I need to change it in the load script:

TimeStamp#(Start_Time, 'YYYY-MM-DD hh:mm:ss.fff') as Start_Time,

Doing this in the load script I see my chart is disappearing .

Your input is very helpful though.

[ashis]

Hi Aruna,

Thank you for your quick response . It did not work. I believe in our db we have Nano seconds so while calculating the difference I need to consider second and Nano second.

I wanted to skip the Nano second part though.