New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLite updating DATE column return wrong format #5482

Closed
lancetw opened this Issue Feb 25, 2016 · 6 comments

Comments

2 participants
@lancetw

lancetw commented Feb 25, 2016

dialect: SQLite

UPDATE posts SET start_date='2016-02-24 16:00:00.000 +00:00' WHERE id = 123;

sequelize@3.4.0 (expected)

{ startDate: Thu Feb 25 2016 00:00:00 GMT+0800 (CST) }

sequelize@3.19.3 (wrong format)

{ startDate: '2016-02-24 16:00:00.000 +00:00' }


No problem when execute INSERT, both return local datetime (i.e. CST) .

@janmeier

This comment has been minimized.

Member

janmeier commented Feb 26, 2016

Can you show a SSCCE?

@lancetw

This comment has been minimized.

lancetw commented Feb 28, 2016

@janmeier Thanks your response, please clone this repository to see what happened.
https://github.com/lancetw/sequelize-issue-5482
The bug only discovered on an existing sqlite DB file.

@janmeier

This comment has been minimized.

Member

janmeier commented Feb 28, 2016

const testData = {
  startDate: '2016-02-28T00:00:00+08:00',
  endDate: '2016-02-28T00:00:00+08:00',
  openDate: '2016-02-28T00:00:00+08:00',
  closeDate: '2016-02-28T00:00:00+08:00',
  title: 'test only',
  content: 'test only',
  uid: 1
};


return sequelize.sync({
  force: true,
  logging: console.log
})
  .then(() => Post.create(testData))
  .then((cData) => {
      const cDataJSON = cData.toJSON()
      console.log(cDataJSON.startDate)
      console.log(cDataJSON.endDate)

      return Post.findOne({
        where: { id: cData.id }
      });
    }).then((post) => post.update(testData)
    ).then((uData) => {
      const uDataJSON = uData.toJSON();
      console.log(uDataJSON.startDate);
      console.log(uDataJSON.endDate);
    })
  .finally(() => sequelize.close());

Works fine for me

Executing (default): DROP TABLE IF EXISTS `posts`;
Executing (default): DROP TABLE IF EXISTS `posts`;
Executing (default): CREATE TABLE IF NOT EXISTS `posts` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `uid` BIGINT NOT NULL, `order` INTEGER DEFAULT 0, `fee` TEXT, `type` INTEGER DEFAULT 0, `prop` INTEGER DEFAULT 0, `start_date` DATETIME NOT NULL, `end_date` DATETIME NOT NULL, `open_date` DATETIME NOT NULL, `close_date` DATETIME NOT NULL, `date_type` INTEGER DEFAULT 0, `title` VARCHAR(255) NOT NULL, `content` TEXT NOT NULL, `ocname` VARCHAR(255), `cid` BIGINT, `lat` DOUBLE PRECISION DEFAULT NULL, `lng` DOUBLE PRECISION DEFAULT NULL, `country` VARCHAR(255), `city` VARCHAR(255), `place` VARCHAR(255), `zipcode` TEXT, `address` TEXT, `url` TEXT, `img` TEXT, `file` TEXT, `status` INTEGER DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`posts`)
Executing (default): INSERT INTO `posts` (`id`,`uid`,`order`,`type`,`prop`,`start_date`,`end_date`,`open_date`,`close_date`,`date_type`,`title`,`content`,`lat`,`lng`,`status`,`createdAt`,`updatedAt`) VALUES (NULL,1,0,0,0,'2016-02-27 16:00:00.000 +00:00','2016-02-27 16:00:00.000 +00:00','2016-02-27 16:00:00.000 +00:00','2016-02-27 16:00:00.000 +00:00',0,'test only','test only',NULL,NULL,0,'2016-02-28 17:25:44.705 +00:00','2016-02-28 17:25:44.705 +00:00');
Sat Feb 27 2016 17:00:00 GMT+0100 (CET)
Sat Feb 27 2016 17:00:00 GMT+0100 (CET)
Executing (default): SELECT `id`, `uid`, `order`, `fee`, `type`, `prop`, `start_date` AS `startDate`, `end_date` AS `endDate`, `open_date` AS `openDate`, `close_date` AS `closeDate`, `date_type` AS `dateType`, `title`, `content`, `ocname`, `cid`, `lat`, `lng`, `country`, `city`, `place`, `zipcode`, `address`, `url`, `img`, `file`, `status`, `createdAt`, `updatedAt` FROM `posts` AS `posts` WHERE `posts`.`id` = 1;
Sat Feb 27 2016 17:00:00 GMT+0100 (CET)
Sat Feb 27 2016 17:00:00 GMT+0100 (CET)

I appreciate you taking the time to create a repo, but a copy-pasteable test-case without external dependencies is a lot easier to debug. Do you see any differences between my test case and yours that I missed?

@lancetw

This comment has been minimized.

lancetw commented Feb 28, 2016

@janmeier It also works fine for me when sequelize.sync({force: true}).

The repository to show this bug only discovered on an existing sqlite DB file.
I have tried your test code on the DB file, same issue.
lancetw/sequelize-issue-5482@8d0f7be

@janmeier

This comment has been minimized.

Member

janmeier commented Feb 29, 2016

Seems the data types in the sqlite files are written in lower case - which means we don't find the right parser for the type. We should probably do a to upper case before fetching the parser here

@janmeier janmeier closed this in 20a1f42 Mar 30, 2016

@lancetw

This comment has been minimized.

lancetw commented Mar 30, 2016

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment