Calculate number of mins between 2 dates

[moteb_marei]

Dear friends:

I have these 2 formates of dates:

Start :                                                              End:

2019-11-14T08:37:23.245+02:00          2019-11-14T11:28:46.236+02:00

And want to calculate number of minutes between the 2 dates but couldn't.

I tried these:

1- Interval(End-Start,'mm') as duration...... but  it didn't work

2- (End-Start)*24*60 as duration....didn't work

3- Interval(Match(End-Start,'mm')) as duration...... didn't work

4- Minutes(End-Start) as duration didn't work.

Please help.

[tresesco]

Could you explain your timestamp? The first part of it is YYYY-MM-DD - is understood, but  next part is not very clear to me. Also explain your expected output as per your example.

[anushree1]

Try this:

Interval(Timestamp( '2019-11-14T11:28:46.236+02:00','YYYY-MM-DD hh.mm.ss')-Timestamp( '2019-11-14T08:37:23.245+02:00','YYYY-MM-DD hh.mm.ss'),'mm')

[moteb_marei]

This 2019-11-14T08:37:23.245+02:00 comes from ODK by default as a metadata,

the output should be the duration in minutes between start and end

[moteb_marei]

It didn't work.

And we should to put the names of the fields (end-start) not each single record 

[jonathandienst · Accepted solution]

The first step is to get QS to recognise the timestamp as a date time value, otherwise QS will see the timestamp as a string. That's why none of your expressions worked.

Is the time reflected in the timestamp GMT/universal time to which the +2 timezone must be added? 

Is the timezone always +2? If you only want the interval, then the timezone won't matter. You could use:

=Timestamp#(SubField('2019-11-14T08:37:23.245+02:00', '+', 1), 'YYYY-MM-DDThh:mm:ss.fff')

This is now a valid date/time value which can be used in date arithmetic, or in the interval() function. If you prefer, wrap the value in a Timestamp format function to get a more friendly format:

=TimeStamp(Timestamp#(SubField('2019-11-14T08:37:23.245+02:00', '+', 1), 'YYYY-MM-DDThh:mm:ss.fff'), 'YYYY/MM/DD hh:mm:ss')

To convert from a UTC to local time, apply the ConvertToLocalTime() function/ See the help for more.