从 PostgreSQL 中的时间间隔获取一周中的某些天数(周末)
给定postgres中的2个时间戳,如何在不计算整个周六和周日的情况下计算时间差?
或者
如何计算给定时间间隔内星期六和星期日的数量?
Given 2 timestamps in postgres, how do you calculate the time difference without counting whole Saturdays and Sundays?
OR
How do you count the number of Saturdays and Sundays in a given time interval?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
以下函数返回两个日期之间的完整周末天数。 由于您需要一整天,您可以在调用该函数之前将时间戳转换为日期。 如果第一个日期不严格早于第二个日期,则返回 0。
示例:
要获取除周末以外的天数,只需从上面的函数中减去天数即可:
The following function is returning the number of full weekend days between two dates. As you need full days, you can cast the timestamps to dates before calling the function. It returns 0 in case the first date is not strictly before the second.
Examples:
To obtain the number of days except full weekend days, simply subtract the number of days from the function above:
您可能会发现这确实很有帮助:
You might find this really helpful:
(天/7)*2 + 最后 (天%7) 天的周六/周日数量。
(days/7)*2 + number of sat/sun in the last (days%7) days.
这应该回答您问题的第二部分:
之后制作相应的
count_non_weekend_days
就很简单了。This should answer the second part of your question:
Making a corresponding
count_non_weekend_days
is simple after that.最好的解决方案是使用日历表。 这非常有用,您可以做各种有趣的事情 - 包括计算两个日期之间的工作日数或计算两个日期之间的假期数。
该表通常是提前填充的 - 比如说 20 年,并适当标记了众所周知的假期的日期。 如果假期发生变化,您可以偶尔维护该表以将这些日子标记为假期。
更多信息请参见此处 和 在这里。 它使用 MS SQL Server,但也很容易移植。
The best solution to this will be to use a calendar table. This is incredibly useful and you can do all sort of interesting things - including counting the number of working days between two dates or counting the number of holidays between two dates.
This table is usually populated in advance - say for 20 years with the date for well known holidays appropriately tagged. If holidays shift, you maintain the table once in a while to mark the days as holidays.
More info here and here. This uses MS SQL Server, but is easily ported as well.
您可能会发现这篇博文很有帮助:http://www.depesz.com/index.php/2007/12/27/how-many-1sts-of-any-month-were-sundays-自-1901-01-01/
You might find this blogpost helpful: http://www.depesz.com/index.php/2007/12/27/how-many-1sts-of-any-month-were-sundays-since-1901-01-01/
这将计算两个日期之间的某一天的数量:
This will count the number of a certain day between two dates:
我建议你创建一个函数,想什么时候用就什么时候用,少写一些; )
上面的代码将创建一个 sql 函数来计算并返回周末天数(周六、周日)。
这样你就会更灵活地使用这个功能。
之后,您可以使用函数仅返回间隔内的周末天数。
下面是要使用的示例:
此选择必须返回 4,因为第一天和第二天是星期一,中间有 2 个星期六和 2 个星期日。
现在,要仅返回工作日(没有周末),如您所愿,只需进行减法,如下例所示:
I suggest you to create a function for use whenever you want and write less ; )
This code above will create a sql function that count and return the amount of weekend days (Sat, Sun) .
Just the way you will have more flexibility to use this function.
After that, you can use the function to return only the number of weekend days in a interval.
Here's the example to use:
This select must return four because the first and the second day are Monday, and we have 2 Saturdays and 2 Sundays between them.
Now, to return only business days (without weekends), as you want, just make a subtraction, like the example below: