Right now that application stores that in a rather messy way. Exposing this as a table field would mean that configuration changes (New field/a different field) causes a structural change (alter table). Or you have to go down the 'Date1, Date2, Date3, Number1, Number2' ... path.
In short: I see no way to expose these pieces of information in fields without ugly hacks, hence my interest in HStore/JSON(B) - but datetime based filters would be quite nice to have..
If the datetimes are ISO formatted strings, would regex matching not work for basic filtering?
where json_field.datetime similar to '2016-07-.*'
Of course, it'll probably be slow-tastic, and less convenient than native datetime manipulation, but hey, maybe it's enough to get unblocked?With either approach you'll have to do some mapping in and out of the system (though date probably serialises to string more or less out of the box for most json libs).
[1] http://stackoverflow.com/questions/10286204/the-right-json-d...
That said, you and IanCal have a point: I could just drop the idea of DateTimes and use timestamps/integers - converting queries in my application.
I have a use case where either HStore or JSONB might be neat (The most basic requirement is K/V, so HStore might be good enough. I could use nested structures as well though). Unfortunately some of the values I need to store are datetime values and I'd need to query based on those:
"Give me all documents where the datetime is from this year"
The article claims HStore is basically just strings (might work for bool as well, but not numbers/datetimes). JSON can express strings, numbers, booleans - but not datetime values.
Has anyone done something similar? Is that even possible?