javascript - sequelize where date clause - Stack Overflow

admin管理员组

文章数量:1323150

• OS: Linux (Lubuntu)
• language: Javascript (Node js)
• framework: express js
• Database: mysql
• "data" is a Date field from "activitat" table

When I run the next statement using Sequelize.js

models.TblActivitat.findAll(
                {
                    attributes: 
                    [
                        'codiAct', 'procedencia', 'pacient', 'proces', 'prova', 'rmn', 'realitzador',
                        [Sequelize.fn('date_format', Sequelize.col('data'), '%d-%m-%Y'), 'data']
                    ],
                    include: models.TblTecnics,
                    where: {"data": '2016-10-20' },   //dataAAAAMMDD
                    order: "registre DESC"
                }).then(function(tblActTec){
                    ...
                });

... I'd have to get some records, but I get nothing

running on mysql I get the results:
mysql> select data from activitat where data = '2016-10-20';
+------------+
| data |
+------------+
| 2016-10-20 |
| 2016-10-20 |
| 2016-10-20 |
| 2016-10-20 |
+------------+

If I see the console of the Node server. When the satement before is executed, it shows as follow:

Executing (default): SELECT activitat.codiAct activitat.procedencia, activitat.pacient, activitat.proces, activitat.prova, activitat.rmn, activitat.realitzador, date_format(data, '%d-%m-%Y') AS data, tecnic.codiTec AS tecnic.codiTec, tecnic.nom AS tecnic.nom FROM activitat AS activitat LEFT OUTER JOIN tecnics AS tecnic ON activitat.realitzador = tecnic.codiTec WHERE activitat.data = '2016-10-19 22:00:00'
ORDER BY registre DESC;

my question is:
I wrote "2016-10-20" as the where clause. When I do it in mysql it shows the results I hope, but when sequelize is executed it changes the value of data clause by "2016-10-19 22:00:00" (two hours before "2016-10-20 00:00:00"!!). Why?

I remeber you that "data" field is a Date field (not a DateTime field)

Thank you very very much!!

• OS: Linux (Lubuntu)
• language: Javascript (Node js)
• framework: express js
• Database: mysql
• "data" is a Date field from "activitat" table

When I run the next statement using Sequelize.js

models.TblActivitat.findAll(
                {
                    attributes: 
                    [
                        'codiAct', 'procedencia', 'pacient', 'proces', 'prova', 'rmn', 'realitzador',
                        [Sequelize.fn('date_format', Sequelize.col('data'), '%d-%m-%Y'), 'data']
                    ],
                    include: models.TblTecnics,
                    where: {"data": '2016-10-20' },   //dataAAAAMMDD
                    order: "registre DESC"
                }).then(function(tblActTec){
                    ...
                });

... I'd have to get some records, but I get nothing

running on mysql I get the results:
mysql> select data from activitat where data = '2016-10-20';
+------------+
| data |
+------------+
| 2016-10-20 |
| 2016-10-20 |
| 2016-10-20 |
| 2016-10-20 |
+------------+

If I see the console of the Node server. When the satement before is executed, it shows as follow:

Executing (default): SELECT activitat.codiAct activitat.procedencia, activitat.pacient, activitat.proces, activitat.prova, activitat.rmn, activitat.realitzador, date_format(data, '%d-%m-%Y') AS data, tecnic.codiTec AS tecnic.codiTec, tecnic.nom AS tecnic.nom FROM activitat AS activitat LEFT OUTER JOIN tecnics AS tecnic ON activitat.realitzador = tecnic.codiTec WHERE activitat.data = '2016-10-19 22:00:00'
ORDER BY registre DESC;

my question is:
I wrote "2016-10-20" as the where clause. When I do it in mysql it shows the results I hope, but when sequelize is executed it changes the value of data clause by "2016-10-19 22:00:00" (two hours before "2016-10-20 00:00:00"!!). Why?

I remeber you that "data" field is a Date field (not a DateTime field)

Thank you very very much!!

• javascript
• mysql
• express

Share Follow asked Oct 7, 2016 at 22:55 jordi M.jordi M. 3111 gold badge11 silver badge44 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

Sequelize converts the string you pass in to a Date object, which has a time assigned to it. If you want to select records on a certain date (rather than at an exact time) you can do so like this:

 date: {
     $lt: new Date('2016-10-20'),
     $gt: new Date(new Date('2016-10-20') - 24 * 60 * 60 * 1000)
 }

Or in your case:

models.TblActivitat.findAll(
            {
                attributes: 
                [
                    'codiAct', 'procedencia', 'pacient', 'proces', 'prova', 'rmn', 'realitzador',
                    [Sequelize.fn('date_format', Sequelize.col('data'), '%d-%m-%Y'), 'data']
                ],
                include: models.TblTecnics,
                where: {"data": {
                    $lt: new Date('2016-10-20'),
                    $gt: new Date(new Date('2016-10-20') - 24 * 60 * 60 * 1000)
                } },   //dataAAAAMMDD
                order: "registre DESC"
            }).then(function(tblActTec){
                ...
            });

I've discovered a new field type when you define the fields of the table in Sequelize. This is the DATAONLY type (instead of DATA type). So, the field definition follows as:

        data:
        {
            type:   DataTypes.DATEONLY,
            validate:
            {
                notEmpty: 
                {
                    msg: "-> Falta data"
                }
            }
        },

With this file type (DATAONLY) it only considers de date part (not the time part)

本文标签： javascriptsequelize where date clauseStack Overflow