0000-00-00 00:00:00 timestamp is returned as a string

[agalazis]

this should be equal to new Date(0) I believe. Here is what i 've done to make sure I always get a date:

if (typeof result.my_col ==="string"){
              result.my_col=new Date(0);
}
/*instanceof Date can also be used
or to cover only that case something like this:
result.my_col ==="0000-00-00 00:00:00"
but I don't remember if the above was the exact string given
*/

The rest of the timestamps is returned as dates so I wouldn't expect this behavior

[dougwilson]

In MySQL 0000-00-00 00:00:00 is special and cannot be represented in any way by a JavaScript Date object. If you cannot handle them, then I recommend turning off support for 0000-00-00 00:00:00 in your MySQL server all together.

[dougwilson]

Another option if you want them to be new Date(0), you can always implement your own type casting (documentation at https://github.com/felixge/node-mysql#type-casting). Please note that now your zero dates will not round-trip, because since new Date(0) === 1970-01-01T00:00:00.000Z, if you used that Date object in an insert or update with this library, you'll end up with 1970-01-01 in your database columns instead of the original 0000-00-00.

[agalazis]

A no that was used as the default value(Inserted by the database it self only javascript wouldn't be able to insert it anw) but still It would be nice to mention this issue in the documentation though so that developers can handle it as they want.
(Or even propose that people won't use it as a default value - I was thinking that "0000-00-00 00:00:00" as a non existent value could correlate to some beginning of time like "1970-01-01T00:00:00.000Z" so even if I insert 1970-01-01T00:00:00.000Z in the application(and only in the application) they would be equal but of course in the database they would be different and different systems would interpret it differently and maybe different developers would interpret it differently so I understand why it is considered as bad design. Thanks a lot for your time and input!)

[sidorares]

I use INVALID_DATE = new Date(NaN); for that - https://github.com/sidorares/node-mysql2/blob/51bb7a7b617f9e2d78222405e0f73033d952bfb3/lib/packets/packet.js#L163

[dougwilson]

@sidorares does mysql2 round-trip the NaN dates back to 0000-00-00 on insert?

[sidorares]

Probably not, but I might add invalid date -> 0000-00-00 serialisation. If you think it's a good api, let's document and make it standard thing

[dougwilson]

That sounds fine, but we also need to flesh out how to handle "zeros in dates" as well. i.e. we would also need to convert 2015-00-00 12:32:43 into a JavaScript Date that can round-trip if we're going to say we always return a Date object.

[renaco]

Hello, I found this gist and could resolve my case
Gist convert-UNIX-timestamp.js