How can I make Glide read a date as a string?

[marshru]

I’m working on a little app which reads data from an API end-point. I have to do a little messing around to make appropriate time zone adjustments. I originally wanted to keep the adjusted dates in date format so I can filter and sort on them but I haven’t been able to find a mechanism for doing this that works on all platforms (particularly IOS). So - I have settled on just saving them in text form for display. I still can’t get them to display properly on IOS, even when I put them into a larger string with other text. Glide is being very clever and interpreting the date as a date (tried long and short form variations - all the same).

How can I make Glide ignore the date in a string and just render the thing as I formatted it?

I guess another possibility is that this is happening at the client side but I’m sceptical.

[Darren_Murphy]

Here is how I usually handle date formatting:

• Start by using a math column to convert the raw date into an integer in YYYYMMDD format. Use the following formula:

Year(Date) * 10^4
+ Month(Date) * 10^2
+ Day(Date)

• The above ensures that you have a representation of a date that is completely non-ambiguous.
• Next use that integer value to convert back to a date with a Text to Date column. You need to specify the format as yyyyMMdd and apply an appropriate utc offset:

• Finally, use a Format Date column to convert the generated date to a string, applying whatever format you need.

The above should work reliably on all devices, and regardless of device/browser regional/language settings.

[marshru]

Thanks for this - I also thought this was the surefire fix, but for some reason it is not working. This is what I have in the dev environment:

where airDatetxt is:

AirDatexformed is the text to date:

and AirDateFmt is the format:

And the date doesn’t show up at all on the IOS device:

Just to finish it off - the html generated in the dev environment has it as a list item which I think looks ok but I’m no expert:

What am I missing?

[Darren_Murphy]

What happens if you change the Text to Date column to use medium date format instead of short?

[marshru]

No difference - I tried all three formats and date & time

Dev environment is Windows by the way.

[Darren_Murphy]

Okay, I’m not really sure why that isn’t working for you. I guess my suggestion at this point would be to display the calculated value from each step on the screen. This would at least help identify at which point it’s breaking down on the IOS device.

[ThinhDinh]

@Darren_Murphy I tried developing the flow on Safari, and it doesn’t return any value for Text to Date column.

However, it works with Format Date directly.

@marshru Can you try just using Format Date and not anything else?

[marshru]

Sure - the text to date pattern was yyyyMMdd but I will try with just date format.

[marshru]

Ok - so, here’s the problem. It works on the original date field, but that field was never a problem, all the date displays work on that. It does not work on the adjusted date which is created with a snippet of JS to change the date to a different time zone:

Whatever I do with this date value it will not display properly on IOS. If I just display this column directly it shows “invalid date”, if I display the formatted date from above it is empty. I tried something else a while back based on a youtube video (I think using an Excel formula from memory) and that displayed a date but, unbelievably, the original datetime pre-time zone adjustment (I promise I was using this tzAdj column, I double-checked). I know IOS has some problems with JS generated dates but I would have thought that this would have been worked around with the above fix. I may have to find another way of adjusting the time zone?

[marshru]

I didn’t see this before. Yes, good idea, would have saved @ThinhDinh some time, so sorry both.

The answer is, none of them work on the iPhone. Not even the first Math column. So, it’s the JS date transformation (see below). So, that now being the problem I have to solve, anyone know a reliable time zone adjustment approach that doesn’t require Javascript?

Thanks both of you for your patience by the way and sorry for wasting your time…

[ThinhDinh]

Yeah, that JS column will have problems with iOS/MacOS since Apple doesn’t like the underlying format of the date column, I guess.

What exactly are you trying to do there? To convert the date to the signed-in user’s timezone?

[marshru]

Yes, the date returned is in the time zone of the region where the series is made, and i’m converting it to the viewer’s time zone.

[ThinhDinh]

Does your date come with the hour part?

[marshru]

Well, it’s a json object returned from the API e.g.
{“air_date”:“2023-09-12”, … }
so, no hour there. But the js which parses air_date out creates a Date object:

and that has an hour.

Problem is that it puts it into the local time zone obviously so I have to the make a time zone adjustment per above.

[ThinhDinh]

Is that 10AM the correct time locally?

[marshru]

Nope - no idea where it’s getting that from. It’s 2pm here.

I thought maybe it was the time in the US somewhere (east coast somewhere).

[ThinhDinh]

@marshru

Well, it’s a json object returned from the API e.g.
{“air_date”:“2023-09-12”, … }

How did you get the API result in the first place?

[marshru]

using a FetchJSON column with no formatting.

from the TMDB documentation:

[ThinhDinh]

I think it’s only useful if they have the exact hour of airing, say 8PM, then you convert the datetime value to the user’s timezone so they know when exactly that will be aired in their time. If it doesn’t have a time value then your conversion is likely to be wrong.

[marshru]

True - it won’t be “right”. But it should still work shouldn’t it? Or is the resulting date from the time zone adjustment not a valid date?