It sounds simple, but still I am not sure how to do it:
To record her activities a user needs to enter two timestamps:
when did I start with an activity?
when did I finish the activity?
This data is not entered right when the activity is performed but sometime later.
Timestamps are used to:
calculate the duration of the activity
filter activities for views and reports (eg “all activities that started between 9:00 and 10:00”)
It sounds simple, right? Just add two date entry fields to a form and be done.
But here’s the catch:
the date entry fields are not so easy to use while using the app on your smartphone with one hand
two date entry fields means entering the date twice (if recording an activity from another day than today)
the timestamps are recorded with the UTC timezone. That’s no problem for display, but for calculation. Example: While in Berlin the timestamp “29.9.23 00:30” is recorded; what gets stored is “28.9.23 23:30” (or even “28.9.23 22:30”). Now, when I want to filter all activities of the 29th this one does not show up. (At least I had trouble with consistent results when accessing date fields in calculated columns, eg Excel, JS.)
What to do? Am I missing something?
My current idea is to split date and time collection.
set the date only once
then enter two time values
I’d like to preset the date to today; but it should be easy to change it to yesterday’s.
Time values are only relevant in 15min increments. I’m thinking about two choice entry fields:
one with all hours (0…23)
one with the minute values (0, 15, 30, 45)
But then I’d need to fuse all this together into two date/time values for the actual timestamps, I guess. How best to do that? Using a JS field or a template?
What if you set the start date/time, and then a duration - which could be a select list of 15 min intervals (15, 30, 45, 60, 75, etc…)?
I think the best way to do this is to create a single integer value from your component parts using a math column, and then convert that to a date/time value using the Text to Date column.
Maybe get the date selected with a choice “today” or “yesterday” (I don’t think activities are logged further back in the past.)
Set a starting time using choices for hours and quarters.
Set duration instead of an ending time using choices for hours and quarters. (Would mean one value less to enter/select.)
And then extract the date from the date choice, eg “2023-09-29”, and add to that the starting time choices, eg “2023-09-23T10:45”, and then get it transformed from a string into a proper date/time value. (Do (almost) the same for the finishing timestamp.)
Interestingly, when I base the choice on a math field it’s “not stable”: I can select “today”, it stays on for a while - and then it unselects itself😳
This does not happen if using an Excel field.
Thanks a ton for your effort, Darren!!! That was really helpful. I’ll go with this approach, I think.
I now added a timezone field to the user table. That way each user can see the dates adapted for his/her timezone. So far it seems to be working mostly ok.
ah, that’s because the return value changes every 10 seconds when the Glide Now value updates. That can be fixed, but if your Excel formula is doing the job for you then you may as well stick with it.
Interesting to know
Thanks!
Yes, the Excel formula does the job.
There is one more thing I need to check with the timezones, though, when switching between users in different timezones… Need to look closer for the pattern.
That was really helpful. I’ll go with this approach, I think.
