Date-time before time 13:00 not showing after copy/paste or export/import of date

Team ID:
go.glideapps.com/o/99cakGb1llPgaq2NXPfy

App ID:
go.glideapps.com/app/JLuyHh6JakfsJ2u6XOsd

Description
When copying a date field either through copy/paste or export/import in the data editor then any date with a time before 13:00 doesn’t show in the data editor or in the layouts where it’s used. Editing the date in the data editor works fine. And changing the time to after 13:00 will make it show. Changing it back to before it’ll disappear. Changing existing times after 13:00 to before 13:00 manually in the data editor also make them disappear.

How to replicate
Copy the dates in blue to below:
image
image
image

The date with timestamp 11:28 is now missing

When editing the field it appears while editing
image

If changing the time manually to 13:28 it reappears after ending editing
image

If changing it back to 11:28 i disappears again. Same if changing the other date-times to any time before 13:00.

What does your first row look like when you click on it? Does it look like “7.1.2023 11.28.11” as well?

Does it work if you change the underlying formatting of row 1 to “7.1.2023 11:28:11” and copy it to row 4?

I think 11.28.11 might be throwing the “reader” off. Usually you see hh:mm:ss, not hh.mm.ss.

1 Like

I agree with Thinh. If the output is buggy for values 12 and below, but not buggy for values 13 and above, it looks like the editor is interpreting that value as a month rather than an hour.

I find dates and times really tricky. Tricky to understand, tricky to work with. It helps to keep things simple to stay out of trouble.

Here are a few tips:

– Writing dates –
Avoid writing dates manually. Use a picker instead. If done via import, be weary of the format of the date column in the source.

– Relations –
Anytime you create a relation with a date, convert the date to either a string (template column) or a number (math column). This will avoid issues with different date formats from device to device.

– How to convert a raw date to an integer in YYYYMMDD format with a Math column –
Year(date)*10^4+Month(date)*10^2+Day(date)

– Date vs Date-Time –
In the Date & Time column, the configuration to “date only” or “time only” only affects what is displayed in the layout editor and app. Under the hood in the data editor, both the date and time are stored.

– On or After // On or Before // After // Before in conditions (visibility, filter, if-then-else columns) –
• On or After // On or Before: Date only. Ignores the time (because of ‘on’).
• Before // After: Date and time. Takes the time into account.

– Change the format of a date –
Using the Format Date column directly on raw date values is not recommended, as it often leads to issues. Instead, transform the date-time value into a date only that Glide can interpret:

  1. Convert the raw date to an integer in YYYYMMDD format using a Math column.
  2. Create a new date from this integer with a ‘Text to Date’ computed column.
  3. Use this new date in a ‘Format Date’ computed column.

The initial 2 steps ensure you’re working with an unambiguous date and a date only rather than date and time. ‘Format Date’ then becomes more reliable.

Thank you to @jeff, @Darren_Murphy, @mattbrowning and @Oscar_V88 for their input.

4 Likes

Excellent summary. Bookmarked.

I’m not sure if this list is “complete”. Once it is, we could add it in the community resources section.

3 Likes

It’s covered most of the important things. “What every Glide developer should know about working with Dates & Times”

3 Likes

It’s enough to start with. :wink:

1 Like

I don’t start anything because I refuse to if things are not already perfect from the outset :joy: The story of my life. I’ll bite the bullet and create the topic for the community resources.

1 Like

Thanks for you input - appreciated.

Bonus info:

  1. The original dates are written to the db from a standard Glide date picker. Nothing custom. The copied dates obviously come from the copy action of which I have tried simple keyboard copy/paste and glide table export/import. Same result.
  2. The first date/time on the list:
    image
    …has the exact same underlying format when being edited manually in the data editor: “7.1.2023 11.28.11”. If I edit it manually in the field where it was originally added via the glide date/time picker it works fine. But copying the field or the underlying text value on edit to another field it fails as described.
  3. I have also tried to copy the text via notepad to get rid of any hidden characters. That didn’t change a thing.
  4. I can confirm that changing to “7.1.2023 11:28:11” (colon instead of dot) works! But in that case I guess that the bug is that the original date from the date picker in my scenario for some reason is shown and exported as “7.1.2023 11.28.11”!

Can’t you guys replicate? I’m in Denmark. Maybe a localisation issue somehow?

If that did indeed come from the date picker then I think it’s a bug. I expected the picker to write a Zulu format under the hood though.

Please open a support ticket.

1 Like