topic Re: Time difference between two columns in App Development

Time difference between two columns

Anonymous — Sat, 16 Nov 2024 16:45:59 GMT

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. 🙂

Re: Time difference between two columns

BrunPierre — Fri, 21 Oct 2022 16:30:51 GMT

What's your expected output from this 20221007115001?

Re: Time difference between two columns

MarcoWedel — Sat, 22 Oct 2022 10:07:50 GMT

please post some sample data and your expected result.

Re: Time difference between two columns

oskartoivonen — Sun, 23 Oct 2022 20:54:05 GMT

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.

Re: Time difference between two columns

Anonymous — Mon, 24 Oct 2022 09:48:25 GMT

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.

Re: Time difference between two columns

MarcoWedel — Mon, 24 Oct 2022 20:30:07 GMT

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);