英文:
How to compare current time to specified time of day and day of the week in PostgreSQL?
问题
我有一个帐户表,并想要为每个帐户指定允许登录的时间,类似于"9:00-19:00,周一至周五",以便以后可以使用一个SELECT请求来检查。这是否可能?
也许我想得不对,也许需要创建像"start_time,end_time,start_day,end_day"这样的列,但我觉得这样做有些多余,我相信应该有一种更简单的方法。
英文:
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.
答案1
得分: 0
选择一个模型周,并将实际营业时间范围存储为tsrange
(timestamp without time zone
的范围)值。这样,您可以轻松强制执行有效的数据,并且通过正确的索引,您所寻找的检查变得非常高效。请参考:
英文:
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论