sailsjs 1.0 createAt and updateAt issue

[yckmars]

Sails version: 1.1.13
Node version: v9.4.0
NPM version:
DB adapter name: sails-mysql
DB adapter version:
Operating system:

---

Anyone has this issue on createAt and updateAt?

AdapterError: Unexpected error from database adapter: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1517579497746' for column 'createdAt'

FYI. i am using sails-mysql and these column datatype are datetime.
I know i can use my code to set createAt or updateAt. However, it seems that the default createAt and updateAt only support number field?

[sailsbot]

Hi @yckmars! It looks like you may have removed some required elements from the initial comment template, without which I can't verify that this post meets our contribution guidelines. To re-open this issue, please copy the template from here, paste it at the beginning of your initial comment, and follow the instructions in the text. Then post a new comment (e.g. "ok, fixed!") so that I know to go back and check.

Sorry to be a hassle, but following these instructions ensures that we can help you in the best way possible and keep the Sails project running smoothly.

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

[akraminakib]

@yckmars I believe what's going on is that when you change the createdAt and updatedAt in your config/models.js to (which I'm assuming yours would look like):

attributes: {
    createdAt: { type: 'string', columnType: 'datetime', autoCreatedAt: true, },
    updatedAt: { type: 'string', columnType: 'datetime', autoUpdatedAt: true, },
    id: { type: 'number', autoIncrement: true, },
}

it associates the fields with datetime column types in your MySQL database. However, '1517579497746' is a unix epoch timestamp represented as a string which isn't a supported format for the datetime colum type in MySQL (as per https://dev.mysql.com/doc/refman/5.7/en/datetime.html).

Maybe what you can do is save the createdAt and updatedAt like so:

attributes: {
    createdAt: { type: 'string', autoCreatedAt: true, },
    updatedAt: { type: 'string', autoUpdatedAt: true, },
    id: { type: 'number', autoIncrement: true, },
}

which will change the column types in the database to varchar(255) and now you can save those unix epoch timestamps as strings.

[yckmars]

Is it possible to store datetime format in MySQL?

[sailsbot]

Sorry to be a hassle, but it looks like your issue is still missing some required info. Please double-check your initial comment and try again.

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

[sgress454]

@yckmars @akraminakib You are on the right track. If you want to store the createdAt and updatedAt values as datetime fields in MySQL, you can update your config/models.js like so:

attributes: {
    createdAt: { type: 'ref', columnType: 'datetime', autoCreatedAt: true, },
    updatedAt: { type: 'ref', columnType: 'datetime', autoUpdatedAt: true, },
    id: { type: 'number', autoIncrement: true, },
}

Note that this will return the fields as JavaScript date objects in retrieved records.

[abalad]

@sgress454
I tried to do what you mentioned.
The updateAt Field seems to work fine, but the field is generating the following error when creating a POST

AdapterError: Unexpected error from database adapter: date / time field value out of range: "1535633407316"

model.js

    createdAt: { type: 'ref', columnType: 'timestamp', autoCreatedAt: true, },
    updatedAt: { type: 'ref', columnType: 'timestamp', autoUpdatedAt: true, },
    deleteddAt: { type: 'ref', columnType: 'timestamp', defaultsTo: +new Date() },

[steveroot]

hi @abalad,
I'm just learning sails and working through platzi course at; https://courses.platzi.com/courses/sails-js/
If I recall one of the earlier sections said sails uses the epoch value for date time (an integer number of seconds since 1970 something) as this avoids issues with time zones, daylight savings time, etc.

I'm assuming all actions that reach the database automatically set/update these timestamps so we wouldn't need to worry about coding for them (I know that's true in Ruby on Rails). Then, if we want to display when a record was updated we can use convert the integer into an appropriately written format in the view (with relevant date time, US or non-US day/month or month/day styling, etc).

That said, if i was trying to use a different format for some other reason, the mistake I normally make is
a) Setting the code to use timestamp but leaving the database as integer
b) setting the database to use timestamp but leaving the code as integer
c) forgetting to run a migration when changing something (in rails, I'm not sure if migrations apply in sails but so far in learning several of my issues were not restarting "sails lift" for the changes to be noticed
d) Using datetime, trying to send an incorrect format to a database (eg "2018-30-08" wasn't recognised but "2018-30-8" was, or using space instead of dash delimiters, etc)

I hope that helps.

Edit: What does "deleteddAt: { type: 'ref', columnType: 'timestamp', defaultsTo: +new Date() }," do?
I read that and assume that each new record contains a deletedAt value of "+new Date()", whereas I'd have assumed it should default to null/empty and the default value is set when a record is to be labelled 'deleted' (as opposed to actually deleted, a.k.a. destroyed)

[bbmattieu9]

I saved my createdAt as bigInt(20) in the mysql database and set my config/model to created_at: { type: 'number', autoCreatedAt: true }, updated_at: { type: 'number', autoUpdatedAt: true },...How can I be sure i am doing the right thing. The createdAt value in the database renders 1541505636418 for the createdAt. how can i be sure it is sending it the correct timestamp.

[bbmattieu9]

@sgress454
I tried to do what you mentioned.
The updateAt Field seems to work fine, but the field is generating the following error when creating a POST

AdapterError: Unexpected error from database adapter: date / time field value out of range: "1535633407316"

model.js

    createdAt: { type: 'ref', columnType: 'timestamp', autoCreatedAt: true, },
    updatedAt: { type: 'ref', columnType: 'timestamp', autoUpdatedAt: true, },
    deleteddAt: { type: 'ref', columnType: 'timestamp', defaultsTo: +new Date() },

I used this and it insert the datetime in the this format "created_at": "2018-12-04T09:45:02.000Z",