handling Date value

[DmitryEfimenko]

I have a simple select statement with a condition having a column equaling to a Date instance. Please see the result. Does not seem like it's an expected behavior.

var date = new Date();
var result = jsonSql.build({
  table: 'users',
  condition: {
    insertdate: date
  }
});

// result.query: select * from "users" where "insertdate";
// result.values: []

Of course, running this results in an error.

I tried to poke around in the code, but could not quite see where to handle this. Any suggestions/help are appreciated.

[artzhookov]

Yeap, it looks like a bug. But what behaviour will be correct? What should be returned from Date instance: .valueOf() or .toISOString()?

As workaround you could call specific method in your code:

var date = new Date();
var result = jsonSql.build({
  table: 'users',
  condition: {
    insertdate: date.valueOf()
  }
});

[DmitryEfimenko]

I think it should be neither of these. Database clients are written to handle Date instances. Here is an example for Postgres showing a new Date() being used as a value for an insert statement.

Handling javascript Date is hairy and people who wrote database clients spent a lot of time making sure they'll be handled correctly. So json-sql should leave variable as-is and let these clients handle it.

The result of jsonSql.build command discussed above should be:

// result.query: select * from "users" where "insertdate" = $1;
// result.values: [ 2017-01-16T16:48:28.494Z ]

The only situation when json-sql should convert value is when separatedValues is set to false. I would not worry about this use-case too much since it opens SQL injection attacks possibility anyway.

However, in this case, the Date needs to be converted to a string in a format recognizable by the database. Here is a case showing that use of .toISOString() in mysql would result in an error. Below are the formats (used by moment.js) recognizable by databases:

database	format
mysql	YYYY-MM-DD HH:mm:ss.S
mssql	YYYY-MM-DD HH:mm:ss.S
postgresql	YYYY-MM-DD HH:mm:ss.SS
sqlite	YYYY-MM-DD HH:mm:ss.S

[okv]

@DmitryEfimenko solution looks good to me, @artzhookov ?

[DmitryEfimenko]

@artzhookov if you point me in the right direction (where the changes need to happen), I can try submit a PR

[artzhookov]

Before I'll able to give you an advice I need to look at the code. I think I'll have time today to find right place at the code and I'll answer after.

[artzhookov]

Please, try json-sql@0.3.10, it should process Date values properly now

[DmitryEfimenko]

Awesome work!
MySql works as expected, however, when using PostgreSql dialect the date value inside values object ends up as a string - not an instance of Date:

result.values: [ 2017-01-16T16:48:28.494Z ] // should be
result.values: [ "2017-01-16T16:48:28.494Z" ] // actual

I think it has to do with this line
Because of that, when querying value back, it looses timezone info.