DB2从时间戳分隔每个季度的周数 [英] DB2 separate number of weeks per quarter from timestamp

问题描述

我试图从每季度的时间戳分开几周，所以应该在每季度1-13周之间，所以我使用函数 week（），但它需要1 -52个星期，所以我把它按照下面的功能划分。

I'm trying to separate weeks from timestamp per quarter so it should be between 1-13 week per quarter so I used function week() but it takes between 1-52 week as whole year so I made it to be divided by function of quarter like below

select Week (EVENTTIMESTAMP) / QUARTER (EVENTTIMESTAMP) from KAP

这里的结果不正确;例如：

The thing here that results aren't accurate; for example it shows:

time stamp 2014-07-06 12:13:03.018 

week number 9

这是不正确的，因为7月是第三季度的第一个月，在6天内应该是Q3的第一个星期不是9个。

which isn't correct because July is first month in Q3 and it's in the 6 days so it should be 1 week from Q3 not 9.

任何建议出错？

推荐答案

你想要一个像$ WEEK modulo 13 在四分之一内获得周数。您将不得不修改模13收益0..12，通过在适当的点添加或减去一个。

You want something like WEEK modulo 13 to get week number within a quarter. You will have to tinker with 'modulo 13 yields 0..12' by adding or subtracting one at appropriate points.

使用ibm db2 sql modulo得到DB2 MOD 函数：

Some minimal Google searching using 'ibm db2 sql modulo' yields DB2 MOD function:

MOD函数将第一个参数除以第二个参数并返回其余值。

The MOD function divides the first argument by the second argument and returns the remainder.

因此 MOD（WEEK（...），13）您可能需要 MOD（WEEK（...） - 1，13）+ 1 ，已经被暗示。

Hence MOD(WEEK(...), 13), except you probably need MOD(WEEK(...)-1, 13) + 1, as intimated already.

您可能需要注意 WEEK（）函数：

WEEK函数返回1到54之间的整数表现了一年中的一周。本周从星期天开始，1月1日始终在第一周。

The WEEK function returns an integer in the range of 1 to 54 that represents the week of the year. The week starts with Sunday, and January 1 is always in the first week.

我很好奇他们如何想出我想这需要1月1日为2000年和2028年的闰年的星期六（1月2日是第2周的开始）。请注意，第53周和（偶尔）第54周将显示为星期Q5的1和2，除非你做某事。另外，星期二2000-03-25将是第一季度的结束，星期天2000-03-26将是由$ code> WEEK（）函数施加的政权的第二季度的开始和一个简单的 MOD（WEEK（...），13）计算。您可能需要调整这一点才能满足您的实际需求。

I'm curious about how they can come up with week 54. I suppose it requires 1st January to be a Saturday (so 2nd January is the start of week 2) of a leap year, as in 2000 and 2028. Note that week 53 and (occasionally) week 54 will show up as weeks 1 and 2 of Q5 unless you do something. Also, Saturday 2000-03-25 would be the end of Q1 and Sunday 2000-03-26 would be the start of Q2 under the regime imposed by the WEEK() function and a simple MOD(WEEK(...), 13) calculation. You're likely to have to tune this to meet your real requirements.

还有一个 WEEK_ISO（）函数：

WEEK_ISO函数返回一个整数，范围为1到53，表示一年中的周。本周从星期一开始，包括七天。第1周是包含星期四的第一周，相当于包含1月4日的第一周。

The WEEK_ISO function returns an integer in the range of 1 to 53 that represents the week of the year. The week starts with Monday and includes seven days. Week 1 is the first week of the year that contains a Thursday, which is equivalent to the first week that contains January 4.

注意根据ISO计划，1月3日可以在上一周的52或53周，12月29日可以在明年的第1周。奇怪的是，似乎没有一个YEAR_ISO（）函数来解决这种歧义。

Note that under the ISO scheme, the 3rd of January can be in week 52 or 53 of the previous year, and the 29th of December can be in week 1 of the next year. Curiously, there doesn't seem to be a YEAR_ISO() function to resolve such ambiguities.

这篇关于DB2从时间戳分隔每个季度的周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！