Collecting timestamps - What's the best approach?

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?

I’m kinda stuck😟
Any suggestions?

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.

Hm… you mean:

  • 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.)

Hm… Will think about it.

Thanks for the suggestion.

Arrrghh, this is what I hate about dates:

I set up a table for a date choice component:

  • a name for the choice, eg “today”, “yesterday”
  • an offset to calculate a date starting from today: 0 (today), 1 (yesterday)
  • a column for the actual date as shown in the image:

This is looking good so far. With the choice I can choose a date and assign it to a database field. This is only for demo purposes:


But when I use a text-to-date column, this happens:

(Never mind that I have to remove the time from the TEXT formatting string in the “Datum” column for this to work.)

Why, why in all the world are the dates in “Xxx” now one day earlier and the time set to 18:00?

And this stays the same when I assign the “Xxx” values to the timestamp component in the form (“Anfang”).

My time zone is eastern European summer time. That’s 3 hours later than UTC. Not 6 hours as the 18:00 time suggests.

What - is - going - on???

Not exactly, I meant start by selecting a date/time with a datetime picker.

But let’s run with your interpretation of my suggestion.
You’d wind up with 4 values to work with:

  • A Date Value representing your start date.
  • A numeric value representing hour of the day
  • A numeric value representing minutes of the hour
  • A numeric value representing the duration

I started typing the rest out, but decided it would be quicker to make a video:

1 Like

That’s fine, but geez… Excel, yuck! :nauseated_face:

Just use a Math column: Now-Offset :wink:

1 Like

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!!!:pray: 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 :astonished: :thinking: :slightly_smiling_face:
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.