yup

i'll be interested to see how monetization works, the gif market seems like an abstract way to make money to me.

http://www.postgresql.org/docs/9.5/s...atype-bit.html

Only partially kidding. There's a wide variety of potential use cases, which means I can't even rule out things like store text and parse them on demand, adding to the rules/grammar as necessary. For example, some parks are open from dawn to dusk.

It sort of depends on what you want to do with the information. If you want to know "is the store open right now? Will it be open 9 hours and 32 minutes from now?" then one method might be best, but if you would like to be able to print a sign giving the store hours, like you have above, another method might be best.

Like, take some really naive approach which listed all the minutes in a day, with a bool that said whether it was open that minute. It would be pretty easy to answer my first question, but potentially a pain in the ass to answer my 2nd.

If you saved it as a set of strings, sort of like you have above, it would be easy to answer the 2nd question and perhaps moderately difficult to answer the 1st.

In the past I had to do this for stock market opening/closing hours. For the most part what we did was list the day/times when the market would change state (from open to closed or closed to open)

This would even kind of work with the "dawn to dusk" scenario if you don't mind doing some kind of wonky hacks (such as defining your own custom time zone)

so you are suggesting i just break the weeks up into 30 min chunks or something, and then use a bit string to represent if the merchant is open or not during each chunk? that's actually a pretty interesting idea.

not sure this is true. those things are view concerns. i'd want to do both of them, plus other things potentially that we don't even know about yet. unless you really have to for performance reasons, i don't think you should design your db structure around views.

these are good points, but i don't consider it a problem that you have to do some transformations on your data, even complex ones, to accomodate one of your views.

speaking of postgres, how easy would it be to use for making a database of docx templates sorted into categories?

This isn't a data vs views question, it's literally a question of "what do you want to do with the data". Even forgetting databases for a minute, your need informs your data structure, and your data structure makes some uses of the data easy and some difficult.

That said, if you want the ability to do something like print a summary of the hours in human readable format, then you should store it in a format that makes it relatively easy to do so. From that to answer more precise questions, you'd need to generate data in some more usable format. Potentially, to save future time, you'd save it as both.

Here's a sort of related example. I work for MapMyFitness. There's a widget where someone wants to enter in a general category of exercises, say "runs and rides". It's not too hard to map these general categories to specific lists of exercise IDs. However, it's difficult to impossible to take a list of given exercise IDs, and "interpret" those into general categories. So it would make more sense to store the categories and generate the IDs on demand, than to do the opposite.

It doesn't make that much sense to talk about data structures when you don't know how you want to use the data. No data structure is appropriate for every task.

P.S. with RDBMSes in particular it is very easy to make completely useless table structures

how crazy do you want to get?

At the least, you'll probably want to use some 24-hour format.

look up bytea.

Yeah that was what I was kind of jokingly suggesting. It's kind of the other extreme from entering mostly human-readable strings and parsing it on demand. I mean you could do something like (store, day_type, open, close) as well to get all open intervals and convert it to human readable format and interpret it accordingly. Probably not super important at the end of the day.

No no no!!!

I can't think of a single reasonable data structure, including candybar's suggestion, where conversion to a human readable format would be more than ten lines of functional code. So it's just a complete non-issue -- why let it dictate how I want the data stored?

I agree in general it's worth thinking about, because sometimes you have a use of the data where some format would make it really awkward, as in you example. But in this case it's clear right away that it won't. Also, you often are using the same data for all sorts of different applications, and you have to made tradeoffs. So often it does make sense to just use the simplest data structure possible, or most efficient one, without regard to the applications, especially if new applications are likely to arise.

i guess the question now is, why is it a joke?

and what are the other reasonable options? postgres 2-dimensional array?

i'm open to anything. i value simplicity though.

I'd just go with the naive solution and store the opening and closing time as timestamps including timezone/daylight savings for each open period. What's wrong with that?

(assuming the schedule isn't fixed from week to week, in which case I'd put the info in a config file).

Well, for starters, you only need to know the open hours, right?

I think you are afraid you are going to end up with a bunch of columns or a bunch of rows and forming a PK on that would be kind of ugly and sloppy.

perhaps not the most simple solution, but if you agree that you only need the open hours:

http://www.postgresql.org/docs/curre...reatetype.html

it sort of ends up looking like a map, but essentially, you can have (morning open, lunch close) (afternoon open, evening close) and use overlap(morning hours, evening hours) to figure out if you need a full day of open or not an you can use the same function to figure out when the company is closed (if that is important).

Something on the order of this:

I can figure out simpler. For example if you know that there are only two hour sets, then the table resolves to a morning and evening hours, but not sure what exactly you are looking for.

Edit to add: If you go with the types route, you can overload operators and use them for the comparisons you need.

I have to believe you've never tried it then.

Is it possible to turn it into "a" human readable format? Yes. That is not going to be hard.

Is it going to be possible to turn it into a *specific* human readable format (i.e. the one that an experienced person would probably come up with?) LOL good luck.

It's way easier to go from description to data than from data to description.

That's just simply not true. Go ahead and give me a input format and an output format that you think is hard.

We used an 24 element array for tracking when different levels of time of use pricing plans where in effect. With one string for work days and one for weekends.

Tell you what, how about you give me your 10 lines, and we see how well it does with unknown input?

ETA: feel free to use something like proposed, i.e. a listlike thing of hours or half-hours or whatever, where true is open and false is closed

the input is always known. the input is what's in the db. the output is something the application needs, like "Monday: 9am-1pm, 2pm-7pm...".

what i'm saying is that for any reasonable choice of structure in the db, the conversion to output such as the above is trivial. So it makes no sense to ask for the conversion function first.

Just a miscommunication?

that might work. the only question in my mind is: is there ever more than a single close period per day (ie, more than a lunch break)? like some random merchant that also closes for afternoon siesta. none of our merchants do, and it's hard to think of any merchant period that i've ever seen that do, so unless we expand to spain it's probably safe, but it would suck to run into that one exception....

EDIT: what are your thoughts on using a postgres 2d array, the outer array being days of week, and the inner arrays being open/close timestamp pairs?

maybe nothing?

daveT's last suggestion is similar to that.

I'm not familiar with some of the postgres specifics, but with the 2d array approach can you easily write a SQL query that returns all the stores that are open at 5pm?

yeah i dunno either, that's a good question though.

By "unknown input" I mean "someone inserted some rows into the database. Now you need to turn that into human readable format"

I would argue that unless the format you are allowing the customer to enter is very strict and limited, you will have a hard time with this. That is, as I said, I think it would be easy to turn it into *a* human readable format, but probably not the one the customer wanted.

Imagine for example that the customer wants you to control his electronic sign. It will be green when the store is open and red when it's closed. It will also display the hours for the customer on the sign, like say it's an LCD display.

You make some fields for him to enter the hours. He is going to want to see THOSE hours printed on the sign, and he'll want it in green when they're open. Do you see how this would be a very hard ask with your chosen structure?

Another similar example would be, say, a cron file. Cron files are different in that instead of expressing ranges, they express times that things happen, but I could easily use the cron format to specify a set of open and close events. I could easily turn this into the suggested bitfield format.

I am saying that you could turn the bitfields back into a cron file. But I don't think you could universally turn them back into MY cron file, the one I gave you originally.

If you can't, then editing really becomes a pain in the ass, because instead of editing MY cron file now I get to edit what you think it should be.

I've run into this myself, as I said, with stock market opening hours. The system that I inherited stored them as a list of stopping and starting times in UTC. Everyone hated this, because it essentially lost the context that it was entered with (i.e. it lost the time zones, and it lost the expressive power of the syntax that was usable to enter the open times, which was cron-like)