Hi,
I think this is a bug so I’ve raised it under this topic, hope that’s OK.
Team ID:
DicJQdwfYZ8IFrkf1dfM
App ID:
KvYy4BKLNCngUHriZrjO
OS/Browser:
Windows/Chrome
Description
I’m importing a CSV file with 3 date fields. All 3 fields have the same data format (date: dd/mm/yyyy) in the CSV file (see screenshot 1) but are treated differently when imported.
date_published and meta_date_delivered come in as Text fields with a mix of dd/mm/yyyy and dd/mm/yy in the same column! (see screenshot 2). Looking into it further, it uses the yy year format if the day (dd) is between 1 and 12, but if the day (dd) is > 12, it uses the yyyy year format!
date_delivered is imported as a Date field (showing as mm/dd/yy, US date format which I understand is tied to my OS date setting).
The far stranger thing is that for the meta_date_delivered field, it is altering the dates from those imported by 1 month for some rows!! I tried 3 different file imports to check this is really happening. See screenshot 3 to see comparisons between the Original Dates (in the CSV file) versus the Glide Dates once they are imported. You can see the difference in those cases (highlighted in orange) is always a month (though Glide may think those are ‘days’, but even if that’s the case, why would it reduce by 1 day?) . Again, when I looked into it, the ones that have the issue (i.e. were imported as 1 month earlier in Glide) are those whose ‘day (dd)’ is between 1 and 12, while any dates with a day (dd) between 13 and 31 has no issues.
Note that there are no differences between the date_delivered (Report DD) in the CSV file and in Glide (green column). They were imported correctly.
Please feel free to test with the test file attached here which is a copy of one of my files with all fields removed except the 3 date fields (see screenshot 1 below). I just imported it to check before I attached it, and see that it has imported all 3 fields as Text fields this time, even though I did not change anything in those columns! Dates in all fields are changed from what they are in the CSV file… best example is date_published below as it’s in descending order.
We can see after Report 38 where the day number (dd) is less than 13, Glide flips the date format and reduces the month by 1, from 12/12/2024 in the CSV to 12/11/24 in Glide.
Dates in dd/mm/yyyy and mm/dd/yyyy are always ambiguous. There is no way for a computer to reliably determine the intended format. I would recommend always import dates as yyyy/mm/DD which is understood internationally. I think starting with that would clear up a lot of issues.
If dates are still being offset by a day, it may have something to do with your timezones offset setting it prior to midnight. Maybe check or uncheck the respect timezone check box in the date column.
I would at least start with changing the format of your dates to yyyy/mm/dd though.
Thanks for that advice @Jeff_Hager - I didn’t know that yyyy/mm/dd was the accepted standard format. I guess I’ll change my source data before importing it and then reformat it in Glide as needed.
I’ll try the options around the ‘respect timezone’ and see what happens. I can’t use that setting in the final app though, as we need fixed dates in those fields regardless of what timezones our users are in.
Share what you find out. Dates are finicky to work with, especially when they come from an outside source and there are so many different formats to consider.
Very helpful post thanks Darren - I followed your approach to test both with Glide tables and Glide Big Tables - something I did not expect would treat data differently, but there is quite the difference! It’s essential I found this out now, as my previous tests used Glide tables, whereas when I import the clients ‘real data’, we will use Big Tables.
Here is my source file - a list of dates working back from 31st Dec '24, using 3 different date formats:
Using ‘Import / Select CSV file’ option to create a new table, these are the results:
Only 1 of the 3 date fields is imported as a date, and even with that, it chops a day off upon import!! So the first date is changed from 31st Dec to 30th Dec, and so on.
The second column is imported as a Text field but it does get it right. It would need to be changed to a date field once imported.
The third column also comes in as a Text field, and initially, it looks ok, until we get to the point where the day is 12 or less, and then it chops 1 off the month, and changes the yyyy to yy!! No logical sense to this, as far as I can make out. See highlighted dates in second screenshot below.
Creating a Glide Big Table first, then importing my CSV file into it, these are the results - 2 of the 3 columns are imported as dates (one of them adds on the time, setting it to midnight). All three are correct in terms of the dates shown. To avoid any ‘time’ issues, I’ll use the yyyy-mm-dd in my CSV and then reformat it in Glide as needed for display.
Could the Glide team look into this please? If there is no fix for Glide tables (as opposed to Big Tables), then could you put a warning somewhere in the docs and/or (preferably) when we are importing files, to let us know that dates may get messed up (maybe it’s already there, and I didn’t see it). Otherwise, a lot of time can be spent trying to figure out what’s gone wrong, or worse, the issue will not be spotted until after the app is live.
