CODEWITHSUNDEEP
javascriptmysqlexpress
jordi M.
jordi M.

Reputation: 31

sequelize where date clause

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!!

Upvotes: 1

Views: 10313

Answers (2)

jordi M.
jordi M.

Reputation: 31

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)

Upvotes: 2

trentjones21
trentjones21

Reputation: 571

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){
                ...
            });

Upvotes: 4

Related Questions