In some DB, For example, MySQL has DATE, DATETIME, and TIMESTAMP types? But in Couchbase Lite, we only have DATETIME, right?
So, what’s the suggestion to store DATE and TIME separately and have a date and time compared function when searching? Thanks for any suggestions.
This really comes down to all the different platforms you use and what makes sense based on that. For data that you have that’s going to be consumed by a mobile app AND some kind of API and possibly web app frontend, I would store it in a friendly way that works for all platforms. Since you are talking about serialization back to some format you can display friendly in all the different platforms - I would look at the whole of what I need to do with that data and then probably save it in a string format that’s friendly to each. Strings are almost always the easiest way to store things and then usually I create some kind of custom serializer or custom properties that handles conversion to whatever I need.
Also sometimes I’ve seen people just save the date into multiple fields in the document in different formats just to make things easier. This is a more of a denormalized solution when database storage isn’t an issue and you are ok with spending time to process and make sure you can keep the multiple fields up to date, which is business logic you would have to add.
Hope that helps some,
@biozal Thank you.
I am also considering using String to store the DATE. But I need to figure out how to select data in a DATE range, For example, data within this week. But I guess some date function will work for this.
We support several date functions:
A range of numbers could be calculated using the number of milliseconds since the unix epoch of a given ISO 8601 date input string for example using the STR_TO_MILLIS function and the BETWEEN statement in SQL++. You can learn about the various comparison operators here:
Does that help a bit?
@biozal It’s a huge help. I am reading the documents, Thanks.