In my app I am storing datetimes in an array. Since putting something in an array always leads to converting the format into a string, I need to convert it back via “Text to Date” when I am accessing the datetime. Taking the “Current Date Time” of Glide leads f.e. to:
3.10.2022, 22:05:15 (d.MM.yyyy, HH:mm:ss)
11.11.2022, 22:05:15 (dd.MM.yyyy, HH:mm:ss)
3.10.2022, 2:05:15 (d.MM.yyyy, H:mm:ss)
… you get it.
Now I need make a conversion for every possibility of missing zeros and then take the non-empty one. An easy solution would be if Glide’s “Current Date Time” wouldn’t skip the leading zeros. The format would always be (for me as a german) dd.MM.yyyy, HH:mm:ss.
Maybe my solution is not the best since i am starting with template.
I start with a template of 0;0;0;0;0;0;0;0;0;0
I wan to “store” the date at the sixt position.
Split text by “;” … get array of 10 zeros
Slice the array in 2 halfs from 1-5 and 7-10
Make array from Slice 1, current date, Slice 2.
Then I have 0…0; 3.10.2022, 22:10:05;0;0;0 as an array
To access the “stored” date i take the sixth single value.
I need fo use this method since my 5000 rows aren’t enough for my project.
If i convert it back via text to date i get 3th of october 2022 using d:MM:yyyy
Maybe i am also forcing a string by starting as a template. Unfortunately you can’t predife an empty date format array using template. Or at least i haven’t figure it out.
By the way i ran into a similar problem using number arrays: make an array out of 10 zeros (number collumn), slice in 2 halfs, combine again and after that the rollup function sum isn’t shown anymore. So i assumed slicing an array always converts the numbers to strings.
At least with this method i ran into this problem.
And true, i should specify: i am talking about the datetime format “short”, which shows in my case
3.10.2022, 2:10:20 (with seconds)
and not
03.10.2022, 02:10:20
When I use date/time->short, I get 06/10/2022, 0:42:34
So even though I use ddmmyyyy, I get a different representation than you do.
This is because of my browser regional settings, and that was my point.
Unless all of the users of your App have identical regional settings, your current method is most likely doomed to failure.
Anyway, you mentioned that you’re trying to save rows. Can you provide me with a bit more context about your use case? Not so much the how, but more about the why, and the end goal.
If I can understand more of the bigger picture, maybe I can suggest a more robust approach to help get what you need.
I am using a variation of the Trebuchet Method, explained in this Video Glide: the Trebuchet Method - YouTube
… And everything works wonderful with it. It’s more work, but you save rows.
I am building an app for Radiology-Trainees, where you can train with online hosted cases. If you trained a cases it’s marked as trained and the time is saved. You can open the calendar view and see which cases you have done in the recent days.
In calendar view, the calendar somehow interpretes the save datetime text almost correct, BUT instead of
5th of October 2022 (03/10/22) for today
It reads
10th of May 2022, so as if the format is american.
Is there maybe a workaround for the calendar view?
7.10.2022, 20:00:00 (-> interpreted wrong in my calendar as 10th of May in 2022)
split text by . or / → [7] [10] [2022, 20:00:00]
single value first → 7
single value second → 10
single value rest → 2022, 20:00:00
make array → [10] [7] [2022, 20:00:00]
join to txt with “.” (or “/” … doesn’t matter for the interpreter) → 10.7.2022, 20:00:00
10.7.2022, 20:00:00 is identified correctly by the calendar as 7th of October in 2022
still a bit cumbersome, but less work the defining every single variation of dd:MM:yyyy.
It would be awesome if the calendar view would have a “european” mode for the interpreter.
The problem with this approach is that you are assuming that the input format will always be the same. And as I tried to point out in my earlier responses, that is a dangerous assumption to make and one that will come back and bite you.
A more robust approach is to use date math, as Glide date math works with the underlying date/time values and is agnostic when it comes to the displayed format.
So, to use your example of 8pm on 7th October 2022. That could be represented in any number of ways, but we want to “force” the displayed format as mm.d.yyyy, hh:mm:ss
What you would need to do is use a series of math columns, with each one extracting a separate one of the date/time components:
Year(Date)
Month(Date)
Day(Date)
Hour(Date)
Minute(Date)
Second(Date)
And then join all the component parts together using a template column.
It’s a cumbersome approach, but it is guaranteed to work 100% of the time, regardless of the users date/regional settings. Whereas your current approach will break.
Day (“08.10.2022, 20:20:20”) returns 10. So the math also assumes an american date.
I just build in a switch, where people can decide if the calendar is based on american or european input format (just in case their browser regional settings are month/day/year).
I just made some tests:
A1 = date of today from entering current day time, thus “08.10.2022 15:16:00”
day(A1) = 8
day(singlevalue(makearray(A1)) = 8 (just the steps i did, not sure if this works in math ^^)
day(template(A1)) = 10
So the problem arises, when i am converting the date into text via template. Somehow math can still interpret the text, but uses an american interpreter. Converting to array doesn’t do anything to the format.
by the way
year(A1) = year(template(A1))
hour(A1) = hour(template(A1))
minute(A1) = minute(template(A1))
second(A1) = second(template(A1))
just day and month is switched
The problem here is not the math column, but the data you are feeding it.
You didn’t explain where the “singlevalue(makearray)” comes from, but I assume it’s from the method you described earlier. And as I explained earlier, that method will give you inconsistent and unreliable results.
I can only repeat what I said earlier. The math column uses the underlying date/time values and ignores any display formatting. Of course, if you feed a template column or some other type of string value into a math column, then all bets are off. But there should never be any need to do that.
I don’t understand the point you are trying to make here, if any?
So is there a different regional browser setting for reading and writing dates?
When I set a date column on the current datetime it writes “8.10.2022 16:10:00.”
But it reads it as string “10.8.2022 16:10.00”.
I know the using templates isn’t a good solution. I was would rather like to predefine an array of 10 empty dates and the write and read directly out of the array. But as far as I know glide doesn’t have that functionality - at least if you’re not using experimental code.
With the the trebuchet method mentioned earlier you “store” values via 2 text columns.
Like:
Old text: “0,0,0,0,0,0,0,0,0,0”
New text: “0,0,0,0, new date,0,0,0,0,0”
Use a button to write the new text to the old text
Then I can access the “stored” new value via the fifth single value of the array (by split text by ,) of the new old text
So I now I just need one row instead of 10 rows in this example.