Date displayed 1 day earlier on forest than what's in db

mathieuh · October 28, 2020, 1:14pm

Hi Forest,

Date format are displayed 1 day earlier on forest.

event_date: {
      type: DataTypes.DATEONLY, // Edited from DATE to DATEONLY
      field: 'event_date',
},

In my db I store 2020-11-11, Forest gives me 2020-11-10.

Looking into this I noticed that forest prints my datetime 1 hour earlier than whats stored in db.

My theory is that forest converts date to datetime so when it gets 2020-11-11 it becomes 2020-11-11 00:00:00 but because it prints it 1 hour earlier it becomes 2020-11-10 23:00:00.
If I didn’t have this 1 hour delta I wouldn’t have notice that forest transform date to datetime.

Any hints to fix this?

Edit: the actual sequelize type for date only is DATEONLY. On the bulk view it only displays the date but on the record view it displays this 2020-11-10T22:00:00.000Z while its still 2020-11-11 in db.

    "database_type": "mysql",
    "liana": "forest-express-sequelize",
    "liana_version": "6.3.13",
    "engine": "nodejs",
    "engine_version": "12.16.1",
    "framework": "express",
    "framework_version": "~4.17.1",
    "orm_version": "5.21.7"

Cheers

Sliman_Medini · October 28, 2020, 2:12pm

Hi @mathieuh,

Thanks for your feedback.
Let’s try to reproduce your issue.

Can you please share some SQL code that recreates a table with the issue?

mathieuh · October 28, 2020, 2:26pm

Sure:

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(255) DEFAULT NULL,
  `event_date` date DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `orders` WRITE;
/*!40000 ALTER TABLE `orders` DISABLE KEYS */;

INSERT INTO `orders` (`id`, `code`, `event_date`, `created_at`, `updated_at`)
VALUES
	(1,'Z1NAITP','2020-11-11','2020-10-28 10:22:03','2020-10-28 16:07:02');

/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
UNLOCK TABLES;

Sliman_Medini · October 28, 2020, 2:58pm

Thank you.

I reproduce an issue on my side.
I am creating the bug ticket, I will inform you about it.

mathieuh · October 28, 2020, 3:00pm

Thank you @Sliman_Medini.

Sliman_Medini · October 28, 2020, 3:58pm

mathieuh · November 9, 2020, 10:38am

Hi guys,

Any update on this? I have no idea how to track it. Will the “TO DO” tag update itself when its done?

Cheers,

jeffladiray · November 9, 2020, 11:17am

Hi @mathieuh,

I have no idea how to track it. Will the “TO DO” tag update itself when its done?

Definitely, the ticket should update once a member of the team starts working on this.

Any update on this?

I would love to help you find a temporary fix in the meantime. The one hour date difference looks like a timezone issue to me (But I might be wrong).

Edit: the actual sequelize type for date only is DATEONLY. On the bulk view it only displays the date but on the record view it displays this 2020-11-10T22:00:00.000Z while its still 2020-11-11 in db.

Did you try using the Date widget with a custom format ?

Let me know if that helps

mathieuh · November 9, 2020, 1:37pm

Hi @jeffladiray,

It’s definitely a timezone issue to me as well because using DATE it displays 2020-11-10 but using DATEONLY it displays 2020-11-10T22:00:00.000Z.
I don’t know where that 2hrs delay comes from but I’m pretty sure the system assumes 2020-11-10 is 2020-11-10 00:00:00 and minus the 2 hours gives 2020-11-10T22:00:00.000Z.

Here is my widget (the edit setting use the same custome format):

Any idea how to fix that 2hrs delay?

jeffladiray · November 9, 2020, 1:43pm

From what I can quickly find, this seems also to be an issue in Sequelize itself, parsing DateOnly fields to javascript Date. (Ex: https://github.com/sequelize/sequelize/issues/4858)

You should be able to change your timezone in the General Settings of your collection (If that helps at all)
.

We will keep you posted once the complete fix is available, but this is the best workaround I can think of.

Hope that helps

mathieuh · November 9, 2020, 2:51pm

Thanks for your insight.

Changing time zone as no effect on this. I tried -2hrs, +10hrs, it still displays 2020-11-10T22:00:00.000Z.

Hopefully it’s fixed soon.

Cheers,

rap2h · November 10, 2020, 11:04am

Hi @mathieuh

As a workaround (thank you for your patience ), maybe you could try to fix the date in your backend since there seems to be a problem with the Sequelize - MySQL - DATEONLY tuple.

According to this issue comment (thanks @jeffladiray for the suggestion), adding a get function in your model field could fix the issue (you may still need to adapt this code to be sure there is no timezone problem in it, according to your actual configuration):

// In forest/orders.js file
event_date: {
  type: DataTypes.DATEONLY,
  get: function() {
    return moment.utc(this.getDataValue('event_date')).format('YYYY-MM-DD');
  }
  // ...
}

One other option would be to use a smart field, but it could be considered overkill:

// In forest/orders.js file
collection('orders', {
  fields: [{
    field: 'fixedEventDate', // Sorry for the name
    type: 'String',
    get: (order) => {
      return moment.utc(order.event_date).format('YYYY-MM-DD');
      // Or something else that could fix the date: find the right conversion zone to apply.
    }
  }]
});

This is a kind of tinkering, but maybe it could help you in waiting for a fix. Feel free to ask if you are facing any problem.

As a side note: P3 is not our higher priority level, so it could take some time before it will be actually fixed. We try our best to fix all issues, but there are some priority rules. That’s why we suggest a workaround for now.

mathieuh · November 10, 2020, 1:11pm

Hi @rap2h,

Actually I got 2 problems here:

the Sequelize issue
the 2hrs delay issue (any idea where that could come from?)

Thanks, I didn’t think about creating a getter. It works now but I had to add 2hrs to the date to get the right date:
moment.utc(this.getDataValue('event_date')).add(2, 'hours').format('YYYY-MM-DD')

If you could point where that 2hrs delay comes from it would be really helpful.

Cheers,

rap2h · November 10, 2020, 1:45pm

@mathieuh Both problems could be due to Sequelize: https://github.com/sequelize/sequelize/issues/2572. A lot of people seem to have similar issues.

In other words:

Dateonly is not fully supported by Sequelize
There seems to be some problems with timezones (Sources: 1, 2, 3, 4 and 5), they are always set even when it is not required.

You could also check the time returned by your server and the database, to understand where the -2h comes from. Maybe you could try to initialize moment with a default timezone corresponding to your configuration (e.g: require('moment').tz.setDefault(timezone)).

After reading these different issues and posts about sequelize and datetime, I’m guess the problem is in Sequelize itself (and its environment). Forest Admin seems to not override the values at this moment. I may b wrong though.

What seems still strange to me is that moment.utc should be good. Could you check with a console log in your server?

const value = moment.utc(this.getDataValue('event_date')).format('YYYY-MM-DD h:mm:');
console.log(value);

Is the date wrong here?

mathieuh · November 10, 2020, 2:24pm

I’ll dig into it.

Yes it’s still wrong, I get 2020-11-02 10:00: while it’s 2020-11-03 in my db.
What I did is I edited the date from forest picking 2020-11-03, I stores 2020-11-03 in the db but retreiving it gives me 2020-11-02 10:00:.

So when it stores it, it looks fine.

rap2h · November 10, 2020, 2:50pm

Yes it’s still wrong, I get 2020-11-02 10:00: while it’s 2020-11-03 in my db.

I guess it’s a problem with the date-time of your server then: if moment returns you a wrong UTC time when you explicitly ask for UTC, it seems it is not aware of the actual date-time. Let me know if you find a solution

mathieuh · November 10, 2020, 6:17pm

I didn’t find the solution…

I’m working on my local machine and mysql time_zone was set to SYSTEM.
So both the server and mysql are running on the same time_zone.

Don’t you reproduce the issue? Like you said it is likely a Sequelize issue and not a Forest one.

Digging into it, I noticed that my createdAt was displayed 1 hour earlier than what’s in db

I’ll still stick with the ugly +2hrs fix for now.

mathieuh · November 10, 2020, 9:18pm

I found the solution.

In /models/index.js I add the line timezone: '+02:00' somehow.

So my event_date displays correctly now, I just need a getter because of the sequelize issue:
return moment(this.getDataValue('event_date')).format('YYYY-MM-DD')

My timestamps are 1 hour ahead though now, they were 1 hour late. It’s not a big deal for me at the moment. I’ll look into it later.

Cheers,

Topic		Replies	Views
Date field, display settings, conditional formatting: strange behaviour Help me!	3	345	March 11, 2022
Date column format Help me!	5	383	October 14, 2021
Date format has suddenly changed from dd/mm/yyyy to mm/dd/yyyy across collections, fields and Forest teams and users Help me!	5	572	December 13, 2021
Converting BIGINT to DatePicker Help me!	4	493	April 19, 2021
Saving date with Datetime picker doesn't keep the same format Help me!	6	279	November 30, 2021

Date displayed 1 day earlier on forest than what's in db

Related topics