Wonder if somebody can help. I am trying to figure out the length of time somebody has worked so have 2 fields as dates/ times. I then have a break time which is a time. I want to show on the page the length of the shift and then also show the length of the shift taking off the break to… When I try this I get ‘NaN’ which I believe is not a number error. I have tried to convert to a number but can’ tget this to work. How do I find the difference between 2 dates and then take off the time field.
As Philip has suggested, you should look to move this logic into the server action query where you are fetching the data. It would be much faster and efficient.
If you are not using a server action, there are date time formatters which allow to calculate differences. Please try using that.
Thanks for getting back to me. I want to do it front end ideally so the user can see in real time there hours are and also validate the data. Then this will be saved into the dB.
I had a look at the date time formats but was struggling to get this to work. Is there any documentation on these? Ideally I think if I can get to a number or number of minutes it should work.
I’ll see if I can have a look today when I’m in Wappler. The problem with the front end date functions is the need an integar to perform adding/subtracting time so it’s just a matter of converting the second time figure into that…which is probably easier in the SQL query.
You wouldn’t need additional columns in your db, you should be able to perform the maths in the Select query.
Ok, so here is something that would work in a custom query. I use Postgres but i’d say there are similar ways with any DB.
In the query below I have just used the start and end date/times and then created a new field that extracts the EPOCH time (which puts it in total seconds for the period) and then simply divided by 60 to get it in minutes.
SELECT
events."start",
events."end",
EXTRACT(EPOCH FROM events."end" - events."start") / 60 AS duration
FROM
events
Now you can use that duration field which is an integer in any front end calculations.
Hope this helps. I’m sure there are other ways. You could possibly create a field for each start or end field that has the corresponding duration in minutes. With EPOCH though, if its a timestamp field (at least in POSTGRES) it will return the total number of seconds on since 1/1/1970 (or whatever the default UTC)
You could get around this though by using CAST and converting the timestamp fields to an INTERVAL type on the fly in the SELECT query.
