Wrong sorting order by Date/Time type column

[sergey-dme]

Hey all,

Sorting order works incorrectly with Date/Time type columns, across whole platform.

Few examples ("Updated" is Date/Time type filed I created):

Current Version: 0.9.79

[sergey-dme]

Mb it will help: the rows containing "00:00" were created using Date Picker. Others – using {{now}}
It seems the problem with values came from Date Picker...

[sergey-dme]

Hey all,

I've just checked using Repeater unformatted Date/Time field values:

Values saved using {{now}} stored in ISO format as:
2021-07-22T19:13:14.244Z

Values saved using Data Picker component:
Fri Feb 01 2019 00:00:00 GMT+XXX (Time Zone Name)

That's why sorting order does not work..

Could you please fix it?

Thank you.

[sergey-dme]

Hi @shogunpurple @aptkingston please take a look at the following example of Flatpicker value behavior:

1. Create an internal table "Table"

2. Create two columns: DateA, DateB - both Date/Time type filed

3. Setup a form for adding new row, keeping only DateA filed in the form

4. For Save Row / Save button Action add DateB filed binding {{ Form.Fields.DateA }} as it shown below:

5. Add a new record/row,

6. Check the row created in the table.

Everything seems okay, but actually not...

7. Navigate to the table records in builder and click edit:

8. Check that DateB has different value in this builder Edit Row form than the value shown in the table

Sorting and calculations starts working correctly for Date/Time fields with binding values from Flatpicker only after manual correction/edit the value using builder Edit Row.

Current Version: 0.9.109

[sergey-dme]

It's interesting that the Default value and Custom Validation functionality works fine with the Flatpick. just tested with {{ now }}
But If I use bind of Form DatePicker for another Date/Time field when saving a row (as in example above), wrong value stored and I need to manually correct it in the Builder - Edit Row otherwise calculations and sorting do not work

[mjashanks]

I can confirm that this is still an issue. My steps

1. Create table with 2 date - DateA and DateB
2. on the new/row screen, remove the DateB field
3. on the new/row screen, in the Save Row action, make DateB be set to Form.Fields.DateA
4. Run your app, create a new row, then edit the row

DateB appears as a random, incorrect date in the Edit screen (though appears correctly in the table component)

tested on version 0.9.119, with a brand new app

[KernOil]

I am having this same issue. when saving a {{now}} or when saving a user-selected date it pushes date time out exactly 7 hours like the time zone is messed up or something.

[aptkingston]

I'll have a look at this 👍

[KernOil]

@aptkingston Thank you. An update on the symptoms I have found.
I submit a row with a date it writes to my SQL DB it + 7 hours. in my SQL DB, I use a calculated column to back the date up 7 hours so the time is correct (date_2). In the DB it reads correctly in my calculated date (date_2) but when viewing date_2 in BB in a table it is now +5 hours from what it says on the SQL DB.

[aptkingston]

@KernOil I think this is just a difference of how the dates are being parsed. We save our dates as ISO strings, which are always denoted in UTC (as highlighted by the Z suffix). For example, if I am in New York (UTC-4) and create a new date as 7AM on 19th October, the ISO string we store will be 2021-10-19T11:00:00.000Z which looks like it's storing the time as 11AM, but the Z is the timezone which represents UTC. The dates just need to be parsed by something that understand ISO strings, and then they should get back to the proper date. Don't worry that it visually appears to be wrong - it's just the format.

This is the reason your calculated dates appear wrong in Budibase - we will be parsing them as ISO strings and accounting for the UTC timezones, which is why we are showing them as some hours in the future, because that's the real value of the calculated dates.

[KernOil]

@aptkingston ok this makes sense to me, however, I do not understand where my time gets messed up. I'm assuming it is the way/format of my SQL table? I'm using timestamp as my datatype should I be using something different?

Testing it today the default {{now}} function will save the correct date and time to my SQL but if I input a time it will give me +7 hours in my SQL table. the internal table works correctly both ways. is it possible there is a bug in the SQL connector?

here is a video of what I'm seeing

Date.Test.mp4