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

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

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?

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;

Thank you.

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

Thank you @Sliman_Medini.

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,

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 :pray:

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?

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)
image.

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

Hope that helps

1 Like

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,

1 Like

Hi @mathieuh

As a workaround (thank you for your patience :pray:), 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. :pray:

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.

Hi @rap2h,

Actually I got 2 problems here:

  1. the Sequelize issue
  2. 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,

1 Like

@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?

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.

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 :pray: :wave:

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 :exploding_head:

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

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,

2 Likes