Unable to find difference between two dates in chart

[#1]

Hi,

I have two datetime fields in a chart, one is EXIT_DATETIME and the other is ENTRY_DATETIME. Both are formatted as YYYY-MM-DD hh:mm:ss, eg. 2015-08-25 17:03:00. I want to subtract one from the other but I am having trouble with the Interval function. If I create an expression (or calculated dimension, it doesn't matter which) in the chart that reads: =Interval(EXIT_DATETIME - ENTRY_DATETIME,'D hh:mm') , QV returns a '-' in that column indicating that it cannot calculate. However, if I create an expression that reads: =Interval('2015-08-25 08:09:55' - '2015-08-25 07:58:55','D hh:mm') , I get the correct answer of 0 00:11 in that field. Why does it work when I pass the function specific dates as strings, but fails when I pass it the column names of the date columns want to calculate the actual differences for?

Thanks for the help!

[jonathandienst]

QV implicitly converts literal strings to date/time values when dat/time values are expected, but it does not do so with fields. I expect your fields are loaded as strings. Either convert the values to date/times during the initial load (preferred) or explicitly convert them in the chart.

During LOAD (Best option):

LOAD ...

     EXIT_DATETIME As sEXIT_DATETIME,

     Timestamp#(EXIT_DATETIME, 'YYYY-MM-DD hh:mm:ss') As EXIT_DATETIME,

     ENTRY_DATETIME As sENTRY_DATETIME,

     Timestamp#(ENTRY_DATETIME , 'YYYY-MM-DD hh:mm:ss') As ENTRY_DATETIME,

Expression:

     =Interval( Timestamp#(EXIT_DATETIME, 'YYYY-MM-DD hh:mm:ss') - Timestamp#(ENTRY_DATETIME , 'YYYY-MM-DD hh:mm:ss'), 'D hh:mm')

[#3]

Thanks for the assistance, I already have in my load script the following line to convert ENTRY_DATETIME to an actual date, if that's one of the options you mean:

LOAD

....

Date#(MID(DATE,2,10) & ENTRY,'YYYY-MM-DD hh:mm:ss') AS ENTRY_DATETIME,

....

FROM

ETC....

I am using MID to leave out some characters at the end of the DATE field before concatenating it to an ENTRY field which has the time component. All my dates come out as YYYY-MM-DD hh:mm:ss with no leading or trailing spaces. This is also what my standard date format is set to in my load script. However this doesn't make a difference

If I instead convert to Date explicitly in my chart, it doesn't work either:

=Interval(Date#(EXIT_DATETIME,'YYYY-MM-DD hh:mm:ss') - Date#(ENTRY_DATETIME,'YYYY-MM-DD hh:mm:ss'),'D hh:mm')

​Thanks

[maxgro]

maybe

- your datetime aren't datetime (check they are right aligned)

- in your chart you have more entry and/or exit datetime for dimension (try to add a rowno() to the load and use it in the chart)

[#5]

Thanks, I found the problem, it wasn't either of these but I appreciate your help 🙂