Strange Date/Time behavior

[Bernd_Grimm]

Hey gliders,

I have just noticed a somewhat strange behavior of the date function.
I create a data set with a date/time specification via the API. November 1st is “translated” as January 11th. This is probably due to the different reading that is usually used in English 11/1/2024 = November 1, 2024 1/11/2024 = January 11, 2024.
I have already played around with the Luxon plugin, but it always ends up with the wrong date in the dataset. apparently the problem only exists with the 1st - as you can see, November 15th is “translated” correctly.
Is this a bug or is there any workaround?
Thank you!

PS: The times are also not transferred correctly, even though the “respect Time zones” column is selected

API-Payload

[Jeff_Hager]

Is this external data being brought into Glide, or Glide data being used externally?

[Bernd_Grimm]

i don’t know exactly what you mean, Jeff. the data is generated in glide and is also processed in glide…

[Jeff_Hager]

You mentioned an API. I wasn’t sure if you are using a Call API to bring data into Glide, or you are using the Glide API to access the data outside of Glide.

So you are using a ‘Call API’ to access data via a ‘Glide API’? If you are transferring data between apps, is there any reason why both apps aren’t sharing a table instead?

[Bernd_Grimm]

Exactly, I use “Call API”.
Yes, the use case is as follows:
Users start an onboarding process and create several tasks via an action. The tasks are given the creation date (Today) and a due date (Today +14 days) when they are created.

[Jeff_Hager]

Disclaimer: I haven’t had a use for the Glide API, so I don’t know all the nuances of it.

Forgive my questions, but just trying to get the full picture. Is the Glide API returning the formatted date instead of an ISO formatted date? How are the dates entered in the source app? Are you using Glide Tables or a different data source in that source app?

Overall, you’re going to fight this for any days between the 1st and the 12th day as they will be seen as months. The problem is that your dates are ambiguous. There is no way to reliably determine if you intend for the date to be MMDDYYYY or DDMMYYYY. The only format that is understood internationally is YYYYMMDD, or a property formatted ISO 8601 date.

Ideally I would prefer that Glide returns the underlying ISO formatted date which is what it actually stores underneath the hood in most cases using a date picker for example. But dates manually entered or potentially set via a Set Column action (using the date special value may be OK) may not necessarily have that ISO formatted date, although Glide will attempt to read it as a date. (For example, you could manually type the words ‘3 days from now’ in a date column and Glide will figure out the actual date, but those words are what is still stored under the hood.) You can easily see the underlying date by clicking on a cell in a date column as if you were going to edit it and see what the underlying value truly is. This is also a case if you use external data sources (ie Google Sheets) where the dates are preformatted before being loaded into Glide.

If the API is returning the formatted version, with underlying values that are still ISO formatted in the source table, then it’s not ideal in my opinion for Glide to pass dates that way. If I’m wrong and Glide does return the underlying value, then I would question why the underlying date values are formatted in DDMMYYY instead of ISO. If you have control over that underlying format of the dates that are returned from the API, then I would consider redoing them in ISO or YYYYMMDD. Otherwise, my only suggestion in the destination table that is calling the API, is to to write the date to a text column, and then use the Text to Date column to convert it to a real date. Not a fan of that method as I feel it’s not always reliable, but unless you can get your date in an international ISO format, the dates are always going to give a little bit of preference to the US style of reading dates.

As for times, I don’t know. Glide seems to do weird things with time and I haven’t found the pattern yet. I’ve seen times be off by anywhere from 1 to 12 hours for various reasons over the years. In your case, I feel like an ISO date with with the appropriate UTC specified time would help. I’m guessing it’s an hour off due to some kind of daylight savings time??? I don’t know. That’s hard to say off the top of my head.

Otherwise, if both apps just share the same table, then you don’t have to bother with the API at all. Data will be shared in real time between apps.

[Bernd_Grimm]

@Jeff_Hager

Forgive my questions, but just trying to get the full picture. Is the Glide API returning the formatted date instead of an ISO formatted date? How are the dates entered in the source app? Are you using Glide Tables or a different data source in that source app?

Overall, you’re going to fight this for any days between the 1st and the 12th day as they will be seen as months. The problem is that your dates are ambiguous. There is no way to reliably determine if you intend for the date to be MMDDYYYY or DDMMYYYY. The only format that is understood internationally is YYYYMMDD, or a property formatted ISO 8601 date.

Hi Jeff, I don’t know if this is ambiguous?! Because, as you can see from the screenshot, Glide itself formats the date in the way I basically need it. If you click on the date field, you can see that the date formatting is saved by Glide itself. I therefore assumed that data entered via the API in the same format cannot be interpreted differently from data entered via the date picker.
What I can see very clearly, however, is that Glide apparently interprets date entries entered via the API arbitrarily. Because you can see (in the first screenshots) that I enter two fields via the API: one date is understood and the other field is transmitted incorrectly.
The literal input - i.e. “from now on 14 days into the future” - works for the moment. On the one hand, the data is not transferred correctly when I use it via the API and on the other hand, the data does not seem to update dynamically, as you can see in the example.
To be honest, I don’t like the text column either, mainly because I think there must be another simpler way for such a relatively trivial task

[Jeff_Hager]

@Bernd_Grimm

I don’t know if this is ambiguous?

Yes, but dates within Glide are always date types. Once you start passing data around through API’s, dates become text strings, and it’s a hole new ballgame to get text strings converted back into date types. I assume the source table is only working with date types. The API can only transmit text strings and the receiving code is responsible for converting each value into it’s respective data type, such as text, number, date, boolean, etc. If I give you a date of 07/08/24, what date am I giving you??? It’s completely ambiguous because I might know, but you don’t know, and no computer knows either.

@Bernd_Grimm

If you click on the date field, you can see that the date formatting is saved by Glide itself

@Bernd_Grimm

Is this the source table that feeds data into the API? This just seems odd to me, and yes I am seeing the same thing in a couple of my apps. I could have sworn that it used to be ISO formatted, but maybe I’m thinking of something else. It’s just a terrible format to store underneath when any form of YYYYMMDD is so much better. So I think we can agree that the API is sending the underlying value, correct?

[Bernd_Grimm]

Hi Jeff, a little addendum: I have now tried the suggestion with the text column. This means that I now write the values in a text column before the API call and get the same effect with regard to November 1st as with all other variants. There does seem to be a problem with the date display, although I can’t understand it, because glide uses exactly this formatting, at least in German-speaking countries.

[Jeff_Hager]

@Bernd_Grimm

write the values in a text column before the API call

I meant on the receiving end. Instead of reading from the API and directly loading into a date column, write it to a text column first and then convert it using Text to Date.

[Bernd_Grimm]

@Jeff_Hager

Is this the source table that feeds data into the API? This just seems odd to me, and yes I am seeing the same thing in a couple of my apps. I could have sworn that it used to be ISO formatted, but maybe I’m thinking of something else. It’s just a terrible format to store underneath when any form of YYYYMMDD is so much better. So I think we can agree that the API is sending the underlying value, correct?

Hey jeff, thanks for your input! But that won’t work because I also want to use the edit function of the task data record to add the date picker to the due date field. This means that if I create a new field there, my entire logic in the tasks will no longer work.

OK - Here is the solution
First, I create 2 columns in the source table, one with the creation date and one with the due date. Then I convert both dates into the English date format (I use the plugin in Luxon for this). Format Date column | Glide
I then pass this manipulated data to the API.
The calculations based on this date also work with this method.

[Jeff_Hager]

Yeah, that should work. I would recommend adjusting your luxon column to output YYYY/MM/DD though. It’s just a lot more foolproof in the long run.