Would it be possible to store (and query) date/time values as well?
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?
The use case I'm looking at is a legacy application that allows you to define various 'dynamic' fields. Think document, extracted data and you can expose fieldA, fieldB and fieldC, while another user wants D, E and F.
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..
One thing you'll run into is that json doesn't have a datetime type so you're left with using either a number of seconds since 1970 (fairly standard) or a string representation [1].
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).
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?