deleted records can be queried in the same second

[Diluka]

What you are doing?

querying records may contain deleted ones

// code here
// paranoid model User
await User.delete(1);
let user = await User.findById(1);

What do you expect to happen?

user is null

What is actually happening?

user maybe not null

Dialect: mysql
Database version: 5.7.16-1debian8
Sequelize version: 4.0.0-2

I've check #5880 and #5897.

1. should use database time rather than servers'
   The server time may be inconsistent with the database time.
2. should use $gt rather than $gte
   For all databases, the time accuracy of the database can only be guaranteed to seconds, so deleted records may be in the same seconds to be queried. To avoid this, the first query condition can not contain the current second, followed by all the time required to the database time.

some refers:
https://github.com/sequelize/sequelize/blob/master/lib/model.js#L80
https://github.com/sequelize/sequelize/blob/master/lib/model.js#L2445-L2452
https://github.com/sequelize/sequelize/blob/master/lib/utils.js#L426-L433

[Wsiegenthaler]

This is more of a bug than the title suggests - despite my server and database both residing in the same timezone, deleted paranoid records are still returned by findAll for a full 8 hours after their deletedAt field is populated (the system is hosted on the west coast, currently 8 hours behind UTC). As op mentions in point #1, the deletedAt field should be populated with the database time for consistency.

I'll open a PR tomorrow to address this as well as issue #2 (should use $gt rather than $gte).

[sushantdhiman]

Fixed 1f8348f