Inserting null or empty string values to timestamp column in postgres causes error

[michalhorcic]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[ ] latest
[x] @next
[ ] 0.x.x (or put your version here)

I have experienced exactly same issue as in issue #1015. I am not able to use update or save with null or empty string value for timestamp in postgres. Getting this error: error: { error: invalid input syntax for type timestamp: "0NaN-NaN-NaNTNaN:NaN:NaN.NaN+NaN:NaN"

I may have found the cause. There is method mixedDateToDate in src/util/DateUtils.ts which is converting value to date. But it will try to convert empty string to date as well ending with undefined date.

Shouldn't there be check for empty string and then return empty string or null? I am still not sure I understand that code correctly. But with that check it works for my use case when I need to nullify some value in database.

Can I push pull request with this fix?

[michalhorcic]

Ok, after more digging I have found out, that problem is in empty string - null works as expected. So in my case it is enough to deal with empty strings where necessary on my side before using repository.update().

Database logging was little bit confusing though. As it shows same output for null and for empty string input:
empty string:
UPDATE "interventions" SET "drive_start_at" = $2, "work_start_at" = $3, "issue_id" = $4, "updated_at" = CURRENT_TIMESTAMP WHERE "id" = $1 -- PARAMETERS: ["1786","2018-06-25T12:35:54.351Z",null,"9161"]

null:
UPDATE "interventions" SET "drive_start_at" = $2, "work_start_at" = $3, "issue_id" = $4, "updated_at" = CURRENT_TIMESTAMP WHERE "id" = $1 -- PARAMETERS: ["1786","2018-06-25T12:35:54.351Z",null,"9161"]

[pleerock]

Shouldn't there be check for empty string and then return empty string or null?

okay we can do it. Feel free to PR.

[adriengibrat]

Invalide date also trigger this bug!
It should be a special check, see https://stackoverflow.com/questions/1353684/detecting-an-invalid-date-date-instance-in-javascript

[herenickname]

I caught a strange error today.
I practically never set dates myself. I can't even figure out where I could get it from.

2020-10-09T18:58:15.672336181Z QueryFailedError: invalid input syntax for type timestamp: "0NaN-NaN-NaNTNaN:NaN:NaN.NaN+NaN:NaN"
2020-10-09T18:58:15.672341863Z     at new QueryFailedError (/usr/src/app/node_modules/typeorm/error/QueryFailedError.js:11:28)

[biels]

Found same bug with '' and 'Invalid Date' from moment()

[trevorallred]

Any updates here? I ran into this bug today.

[biels]

Yeah, it should get fixed. It is should be matter of adding an if.