search

balderdashy / waterline-sequel

A SQL generator for use in Waterline Adapters
MIT License
16 stars 61 forks source link

incorrect date processing #81

Open pepyator opened 8 years ago

pepyator commented 8 years ago

I use sails-mysql which use waterline-sequel module In my model there is a field with "date" type newsletter_date: { type: 'date' } And in mysql table use field with type "date" to when I search by this field Model.find({newsletter_date: '2016-02-23'}) I don't find anything because date convert into the datetime format

MYSQL.find: SELECT ... WHERE newsletter_date = "2016-02-23 02:00:00"

This problem on version 0.5.6-0.5.7 On 0.5.5 works fine.

NachtRitter commented 8 years ago

+1 I have the same troubles in my application.

NachtRitter commented 8 years ago

@particlebanana, hi! Can you please give some feedback about this issue? Cause I locked waterline-sequel version in shrinkwrap file on v.0.5.5, I can't update sails-mysql for using recently added feature of projections in join queries.

particlebanana commented 8 years ago

God I hate date handling. So this change was implemented because people want to do this where before we required an actual javascript date object.

By using the type: 'date' the actual "date" value could be in a lot of different formats. Having date vs datetime works great in mysql but not so great in Mongo, etc where you have Date objects. So we just have a single date type.

If you don't use the auto migrations you could change that model definition to newsletter_date: { type: 'string' } and it should work as expected. If you are using the auto migrations I suggest moving to a standalone migrations library to give you fine grained control over column types.

I'm not sure what the answer here is. Dates are messy and work different in almost every database. In earlier versions we worked around this by requiring Date instances but people hated that. At this point I really just want to rip out all migrations and types and only have models do validations.