Time difference between two columns

[Anonymous]

Hello everyone,
I wanted to calculate the Time difference between two columns like the following code:

//First I plotted the time for 2 columns before the difference.

Timestamp(Timestamp#(@1,'YYYYMMDDhhmmss'),'hh:mm:ss') as Dat1,

Timestamp(Timestamp#(@14,'YYYYMMDDhhmmss'),'hh:mm:ss') as Dat2,

// before Time difference between two columns
interval(Timestamp(Timestamp#(@1,'YYYYMMDDhhmmss'),'hh:mm:ss') - Timestamp(Timestamp#(@14,'YYYYMMDDhhmmss'),'hh:mm:ss') , 'hh:mm:ss') as diff

But it gives me wrong values. In addition, the date and time is the following: 20221007115001

If anyone can help me I would be grateful. 🙂

[BrunPierre]

What's your expected output from this 20221007115001?

[MarcoWedel]

please post some sample data and your expected result.

[oskartoivonen]

When handling dates I always recommend to focus on working with the underlying numbers first, and converting into a human readable date/timestamp/time as the absolute last step. So firstly check whether your @1-field is being interpreting properly by the timestamp#-function, and then do the same for @14-field. A proper timestamp number will be something like 44000,5 or 44325.25. These numbers are easy to subtract in an understandable fashion, the difference between my examples being 324.75, or 324 days and 18 hours.

[Anonymous]

erste spalte steht 155000 = 15:50:05 

zweite spalte steht 163000 =16:30:00

Es muss als richtiges Differenz Ergebnis zurückgeben: 00:40:55 (40 Minuten und 55 Sekunde)

Gibt es so eine andere Methode um die Zeit zu subtrahieren?

Auch bei der Subtraktion von Zeiten handelt es sich nicht um eine Subtraktion zwischen Zahlen. 

[MarcoWedel]

I'm not sure how 155000 is supposed to represent 15:50:05 and the difference between 15:50:05 and 16:30:00 could be 00:40:55, but maybe this example helps:

table1:
LOAD Time(Time#(@1,'hhmmss'),'hh:mm:ss') as Time1,
     Time(Time#(@2,'hhmmss'),'hh:mm:ss') as Time2,
     Interval(Time#(@2,'hhmmss')-Time#(@1,'hhmmss'),'hh:mm:ss') as diff
Inline [
155005, 163000
123456, 234543
012345, 123454
](no labels);