0

I have a table of accounts and want to make it possible to specify allowed time for logging in for each of them, something like "9:00-19:00, mon-fri", so that later it was possible to check with one SELECT request. Is it possible to?

Maybe I'm thinking in the wrong direction and it would be necessary to create columns like "start_time,end_time,start_day,end_day" but it seems redundant to me and I'm sure there is an easier way.

zyxd
  • 3
  • 1
  • as there will be only a few such time restrictions make a new table put your 4 columns in it and reference it – nbk Jun 18 '23 at 14:03
  • we discourage using delimited columns see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk Jun 18 '23 at 14:15
  • Go the `start_time,end_time,start_day,end_day` route. This `9:00-19:00, mon-fri` is hard to validate and is mixing data types and requires effort to unpack to get at what you really want. Basically a recipe for problems. – Adrian Klaver Jun 18 '23 at 16:08

1 Answers1

0

Pick a model week and store actual time ranges for opening hours as tsrange (range of timestamp without time zone) values. This way you can easily enforce valid data, and the check you are aiming for becomes very efficient with the right index(es). See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228