如何在PostgreSQL中比较当前时间与指定的时间和星期几?

huangapple go评论58阅读模式
英文:

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

选择一个模型周,并将实际营业时间范围存储为tsrangetimestamp 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:

huangapple
  • 本文由 发表于 2023年6月18日 21:10:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76500712.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定