[Bug]: Unpredictable movements of the dates in the date picker included in the table widget; are decreased by 1 day.

[felix-appsmith]

Is there an existing issue for this?

• I have searched the existing issues

Description

Currently, in a table widget, if we try to add a new row and one of the fields is a date picker, it will decrease the selected date by one day. For example, if I select 2023-07-09, it will select 2023-07-08. The strange thing about this bug is that it only occurs in a European time zone; it doesn't happen in another time zone.

Steps To Reproduce

I tried to replicate the issue unsuccessfully. However, the user shared the application where the problem can be seen. Make sure you are in a European time zone.

For more context and videos, you can see the conversation with the user: https://app.frontapp.com/open/cnv_11b2uw5o?key=6Ow9FD3H6U0PYcJY6lBMKc7w2XqkaAaq

Public Sample App

https://app.appsmith.com/app/bug-date-simple/page1-64a7d393af033d5dcc48b2e6

Environment

Production

Issue video log

https://www.loom.com/share/405cc2a2c6aa416ea7245721dc33dc76?sid=398a0130-dc0e-4bea-8be4-44fc7b0c83e8

Version

Cloud: Appsmith v1.9.25-SNAPSHOT

[somangshu]

My understanding here is that the timezone that is stored in the DB and selected on client side are different.
Usually this kinda issue happens when the timezone stated in the date object is different from the one you are in. This will show unpredictable results with default date objects. This is specially visible because the user is only viewing dd-mm-yy. If we can see the full date before and after, it will be easier for us to help them out.

[benjamindonze]

This is not unpredictable this is always happening when:

• you have a table widget with a date column with format YYYY-MM-DD
• you add a new row
• you are in european time zone

The problem here is that I set my date widget to use format date format (and display format) YYYY-MM-DD. Apparently when updating the row I can see by printing with in logs that "currentRow" does correctly hold a date in the format YYYY-MM-DD that match the one you select in the widget.

But when adding a row the value hold by "newRow" is in format 2023-07-19T22:00:00.000Z. So probably if you select 2023-07-20 in that widget it actually select 2023-07-20T00:00 in my local time zone and then transform it to UTC time zone. While it should work only with date (with no time)

Well anyway the problem happens in the widget as I can see this happens before the query to the DB. Moreover the issue only happen with new row, row update seems to work.

[benjamindonze]

Looking at the code for DateCell widget I can see that when selecting the date while updating a date, the date is formated with
const formattedDate = date ? moment(date).format(inputFormat) : "";
But this is not done when it is a new row.

Thus this probably lead to keeping the date with time when beeing in newRow mode and so applying some timezoning but correctly handinling the updates.

See below the whole code block:

const onDateSelected = (date: string) => {
    if (isNewRow) {
      updateNewRowValues(alias, date, date);
      return;
    }

    if (isRequired && !date) {
      setIsValid(false);
      setShowRequiredError(true);
      return;
    }
    setIsValid(true);
    setShowRequiredError(false);
    setHasFocus(false);

    const formattedDate = date ? moment(date).format(inputFormat) : "";
    onDateSave(rowIndex, alias, formattedDate, onDateSelectedString);
  };

[amareshvarma]

Is there a way I can contribute to this, any steps to reproduce

[imatpro]

Hello, i have a same problem. when I select date in datepicker (without time precision) the datepicker.selectedDate is at J-1

Thanks for helping

[benjamindonze]

Is there a way I can contribute to this, any steps to reproduce

Well check the 1st post it includes a link to a sample app that shows the issue.

[somangshu]

Thanks for the detailed analysis @benjamindonze. This will help us debug and triage the issue better.
We will take this up internally and prioritise it.

@amareshvarma LMK if you are interested in contributing. This issue might be involved, so my suggestion is to start with other good first issues

cc @keyurparalkar

[amareshvarma]

@benjamindonze

It says 404 error either it is not there or I do not have access

[benjamindonze]

I have invited support@appsmith.com and made that app public so you should now have access otherwise I don't know how I can give you access

[somangshu]

@amareshvarma the app might not be accessible to you. You will have to repro this on your own end.

[dilippitchika]

Hello can you please use - {{moment.utc(DatePicker1.formattedDate)}} instead of using {{Datepicker1.selectedDate}} for now?

@somangshu you are right, this confusion is coming quite often because selectedDate is taking the user's selection and internally using the locale to convert to UTC time.

Our options are

1. Add a new property in the datepicker which is a toggle to remove selectedDate UTC formatting
2. Add a new reference property called {{Datepicker1.localDate}} which will just give the selected date without any utc logic applied to it.

Please suggest if there are other options.

[benjamindonze]

@amareshvarma the app might not be accessible to you. You will have to repro this on your own end.

I can also add you to the app if you want

[PaulMcF1987]

@somangshu @keyurparalkar @dilippitchika

I have been faced with this issue and was pointed here form the discord support channel

I think I have since found a solution although it is early days

I was finding that, no matter what I tried, when using moment that the utc time was always applied, adding 1hour onto the time.
The issue only occurred with the Date/Time input, when viewing in a text widget, the date/time was able to be formatted correctly

I have since added the https://cdn.jsdelivr.net/npm/date-and-time@3.0.2/date-and-time.min.js library and am using {{date.parse(appsmith.store.group.datetime, 'YYYY-MM-DD HH:mm:ss [GMT]' )}}

This seems to have sorted the issue but time will tell

[benjamindonze]

Any news on fixing this issue?

[Nikhil-Nandagopal]

@benjamindonze we haven't picked this up yet and this could be an issue with moment. Try using the solution that Paul has mentioned above and let us know if it works!

[lrytz]

To reproduce in a JS function (I assume it's the same underlying issue):

[Harshithazemoso]

I have picked up the issue and I have tried changing the timezone to europe and connected to datasource but I couldnt reproduce the issue ,are there any steps how to reproduce this or is it resolved ?

[benjamindonze]

Have you tried the public sample app link in the first post : https://app.appsmith.com/app/bug-date-simple/page1-64a7d393af033d5dcc48b2e6

It is very easy to see that the problem is still there. If you simply click "Add new row" and the select a date the save. Then you can see on the left pane "Changed row" that the date is not correct.
For exemple I select date "2024-05-22" and I see after saving in Changed row : {
"date": "2024-05-21T22:00:00.000Z"
}

You can also clearly see behavior is different when you change an existing row

[Harshithazemoso]

Thanks for the response @benjamindonze ,will look into that

[Harshithazemoso]

hello @benjamindonze I have raised a pr for this issue,can you please look into it

[benjamindonze]

I did put a comment and approved the PR

[rahulbarwal]

This is fixed with: #36455