Does anyone know how data is actually stored in Glide? Is it always text - which only get’s project this way or that according to a column’s type?
Why I am asking:
I’m trying to let the user enter a time using two choice components, one for the hour (column HOUR, 07,08,…19) and one for the minutes (column MINUTE, 00, 15, 30, 45). The date/time component is too clumsy to use in the situation the app’s used in.
The date (column DATE) is set automatically when adding a new row to the current date (which includes a time)
Now what I need to do is create the real timestamp for the information recorded from DATE, HOUR, MINUTE.
DATE is of type Date&Time, HOUR and MINUTE are of type Number.
I manage to extract year/month/day from DATE in Math typed columns. But using Year() etc. in an Excel Formula type column does not see to work.
And if I manage to build a date/time value in a Excel Formular column, it’s a number. Does that work with time difference functions? If I want to see a proper date/time, I need to format it using Text(). But that value then isn’t fit for calculations.
I’m a at loss. How is data stored? Which functions to use for date/time operations?
Edit: Actually, just re-reading your question, I see that the current datetime is already added with the form submission. So in the first step (the math column), you should use that datetime value instead of the current time (Now). I’ve updated my answer to reflect this.
For your use case, I would first use a Math column to combine the entered hours and minutes with the datetime value to produce an integer that represents the full datetime in the following format: yyyyMMddhhmm. And then use the Text to Date column to create a true datetime value from that integer. Here is how that would look:
In the above, Date, Hours and Minutes are replaced by the values in the first three columns respectively. Note how the Math column effectively ignores the time component of the DateTime in the first column, and replaces it with the values in the Hours & Minutes columns.
And the configuration for the Text to Date column looks like so:
Great! Thanks, Darren! This worked nicely! I really have to get a feel for how to combine the different column types in Glide. Therein lies the art and power of it, I guess
But something still mystifies me:
The Date is displayed in the table view as 16.7.2023, 14:00:56. That matches my local time (eastern European time zone, UTC+3).
It’s stored as UTC time (2023-07-16T11:00:56.546Z), as a Javascript column shows me.
The time I’m entering in the Hours and Minutes columns of course is also meant to be in local time.
Now, when I convert the Date with the math column Year/Month/Day get extracted on the server, I guess. So, it’s the UTC time year/month/day - which might not match the local year/month/day. (I know, this is rare, but possible.)
However, Hours and Minutes are local (eg UTC+3).
Re. the time zone setting for the Text-to-Date column: That’s specifying the time zone for the value to convert, eg 202307161425 in one of your examples? Hence, that’s meant to be Singapore time and will then be stored as UTC time (eg 2023-07-16T06:25:00Z). Correct?
(By the way, I thought Singapore was UTC+8 (not -8). )
No, all of this happens locally on the users device. Computed columns don’t actually exist server side (well, their definitions probably do, but they are never evaluated server side).
No, I don’t believe that would be an issue.
That’s my understanding, yes.
It is, but for some reason the adjustment in the text to date column seems to work backwards Gave me a minor head spin the first time I tried it, but now I’m used to it.
That’s very interesting to hear: computations happening on the client device.
Indeed it seems like you say: When I enter 16.7.23 00:30 into a Date/Time col it’s stored as 2023-07-15T21:30:00Z. But when I use Day() in the Math column the 16th is returned, ie. the day matching my local time which is known to the browser. That’s good to know.
Of course JS in a JS column sees the raw value.
In the meantime I head figured out UTC-3 for myself, too. (I read something about subtraction in another thread and just tried it.) Strange… but now I know. (They better don’t fix that bug ).
Yes, that’s one reason why Glide Apps tend to be very snappy once loaded.
But it does come with a hidden gotcha. Which is that it opens up the possibility of collisions and race conditions. Imagine two clients updating the same value at exactly the same time. Because the updates happen independently of each other, the last one to sync with the back end wins.
For most use cases this isn’t usually an issue, but when it becomes critical the only effective way to get around it is to delegate updates to a 3rd party. The most common workaround is Webhook->Make->Glide API.
Interestingly Date/Time values seem to be kept as strings whereas Number or Boolean values really are numbers and booleans. That’s what shows a typeof p1 if I pass in different columns.
This explains why I cannot subtract two dates in an Excel Formula just like that. I first have to deserialize them.
And when using JS I need to deserialize Date/Time values, too.
Just to comment a tip: when you write “Excel Formula”, do you mean that you are using Excel formula plugin?
If so, the tip/trick to work with dates with that plugin is to convert dates to text before. The plugin doesn’t know how to handle that Glide’s datetime value.
)
Gave me a minor head spin the first time I tried it, but now I’m used to it.
(I read something about subtraction in another thread and just tried it.) Strange… but now I know. (They better don’t fix that bug 
).