Sails: Datetime type no longer usable with blueprints on Sails 1.0

[Goostavo]

Sails version: 1.1.0
Node version 10.15.0
NPM version: 6.4.1
DB adapter name: sails-mongo
DB adapter version: 1.0.1
Operating system: Ubuntu

---

Repository with simulations for Sails 0.12.x and 1.X:
https://github.com/Goostavo/sails_datetime_test

While updating my sails application from 0.12 to 1.0, i've found out that datetime format is no longer supported. While 0.12 could parse Date() into db specific, there is no such solution available on 1.0 to convert 0.12.x applications.

On model i could achieve the desired behavior by using the code below. But blueprints are not longing working as it cannot parse to Date().

myField: {
      type: 'ref',
      columnType: 'datetime',
      required: true,
      custom: function (dateAttribute){
        return (_.isDate(dateAttribute));
      }
    }

I've entered the 0.12.x sails-mongo code and found out that it was handling the datetime attribute on lib/query/index.js on Query.prototype.parseValue. But these functionality are not present anymore, as the only type used on adapter is the ref.

---

Steps to reproduce in sails 0.12.x and 1.0:
0.12 behaviour.

1. Create an model and add an attribute with myAttrb: {type: "datetime"}
2. Lift Sails with mongodb
3. Using blueprints make a post to create a new model-object. The model will be created with ISODate('YYYY-MM-DDTHH-MM-SS.ssssZ') format.
4. Do a find on using myAttrb. And you will query your data as expected.

1.0 behaviour

1. Create an model and add an attribute with type: "ref", columnType: "datetime"
2. Lift Sails with mongodb
3. Using blueprints make a post to create a new model-object. The model will be created with ISODate('YYYY-MM-DDTHH-MM-SS.ssssZ') format.
4. Do a find on using myAttrb. You won't find any data.

---

I believe that Sails.js need some way to transparently handle datetime format. As many applications relies heavily on timestamp data.

Proposal 1:
-> Extending custom functions and overrides to achieve the desired response. And adding the datetime tutorial on sails-docs.

Proposal 2:
-> Adding an 'datetime' field that is adapter depent. If the adapter dont supports it will only throw an error on sails lift. On adapter implement parsing from js Date() and from adapter standard. Also this can be used on updatedAt/createdAt fields to have the same behaviour as 0.12

With some guidelines i can code the patch!

[sailsbot]

Hi @Goostavo! It looks like you missed a step or two when you created your issue. Please edit your comment (use the pencil icon at the top-right corner of the comment box) and fix the following:

• Provide your Node version

As soon as those items are rectified, post a new comment (e.g. “Ok, fixed!”) below and we'll take a look. Thanks!

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact [email protected]

[Goostavo]

ok, fixed!

[johnabrams7]

Hi @Goostavo,
Thanks for exploring this and providing potential solutions. The sample code is also helpful.

[Goostavo]

Ok! I'll write a sample code and simulate.

[Goostavo]

Done! Sails test on github

The behaviour on sails-disk is exactly the same in sails-mongo adapter. To make it easier to test i leave it to sails-disk for now.

On Sails 0.12 (expected behaviour):

• Model using datetime type.
• Use create blueprints to send a ISODate string. (as it's not possible to send a Date() object)
• Creates sucessfully
• Using Model.find() returns Date() object

On Sails 1.0 (case 1 with validation).

• Model using reftype. With lodash _isDate() validation.
• Use create blueprints to send a ISODate string. (as it's not possible to send a Date() object)
• Gets validation error

On Sails 1.0 (case 2 no validation).

• Model using ref type.
• Use create blueprints to send a ISODate string. (as it's not possible to send a Date() object)
• Creates sucessfully
• Using Model.find() returns string object

[Goostavo]

Possible fix for these closed issues that are currently using workarounds:

https://github.com/balderdashy/sails/issues/4305

https://github.com/balderdashy/sails/issues/4068

https://github.com/balderdashy/waterline/issues/1497

[juanlews]

I'm having the same issue using sails-mongo. Cannot use blueprints for querying after upgrade to 1.0.

[rachaelshaw]

@Goostavo @juanlews in Sails 1.0, the available attribute types were pared down a lot, and type: 'datetime' is no longer supported (here's the list of attribute types available in v1: https://sailsjs.com/documentation/concepts/models-and-orm/attributes#?type). Instead of datetime, we recommend using JS timestamps (type: 'number').

I just went through the upgrade guide again and noticed that there wasn't anything in there about the attribute types that were removed in 1.0, so even though it's a bit late for a lot of people, we probably ought to update the docs with that information.

[Goostavo]

@rachaelshaw
Using JS Timestamps is not good for blueprints API. As usually users want to explicity know the timestamps to make a query. Such as where={"myTimestamp":{">": "2019-04-21T00:00:00.000Z"}}

Why is this functionality was dropped?

I have interest in coding a solution, but I'm not sure if i should try to implement it directly as a new type on waterline, or try as hook.

[rachaelshaw]

@Goostavo the default createdAt and updatedAt attributes use numbers, and you can query them using '>', '<', etc. in much the same way:

// Look up users created in the last day
await User.find({createdAt: {'>': Date.now() - 1000*60*60*24}});

I'm working on updating the upgrade guide with more info about why the available types changed in v1, but in the mean time here's Mike's explanation when some folks were asking about it awhile back: https://gitter.im/balderdashy/sails?at=58be0853f1a33b62758c3cc1

[Clan-Utility]

Same problem here with sails-mongo. The attribute is defined as

createdAt: { type: 'ref', columnType: 'datetime' }

and neither Model.find({ createdAt: { '>' : new Date('2019-01-09')}}) nor Model.find({ createdAt: { '>' : (new Date('2019-01-01')).toISOString()}}) work.

I solved this by using native query.