Grouping mysql records fortnightly

I wa开发者_StackOverflownt to group the records on its timestamp field Fortnightly. I am doing it weekly now but i want it to be fortnightly also. How do i do that ? Is there a specific way to do this like how we group by weeks using WEEK('timestamp','%d-%m-%Y') function ?

In the below image can you see date 21 and 14 coming up for fortnight 10. Any suggestions ?

---

Take the result from week() and divide by 2, convert to an integer (up or down). Because the first week of the year is week 1 (depending on the mode week() uses) and you probably want both week 1 and week 2 to become fortnight 1 so that week 0 becomes fortnight 0, we'll use ceil():

select sum(amount) as amountSum, ceil(week(myTimestamp)/2) as fortnight
  from myTable
  group by fortnight
  order by fortnight;

Reference: week() and its modes

Edit: On getting the last day of the fortnight...

There is no built-in function to get any date from a week number. How pestersome.

Since you are aggregating and grouping on the fortnight (therefore result rows would be in the dozens, probably), I usually first consider calculating such fill-in values in the client code consuming the results, but it turns out it's actually easier to do this in the database due to access to the original timestamps, even though it's ugly.

All you have to do is calculate the end of the week from the original timestamp, but you also have to add 7 more days if that is from the first week so that you get the end of week from the second week of each fortnight. (Based on this article.) I also added the year, just in case. And it also assumes that Sunday is the first day of the week (but check your MySQL instance's default_week_format against the table for week() to double-check).

select sum(amount) as amountSum, year(myTimestamp) as yr, ceil(week(myTimestamp)/2) as fortnight,
  date_add(myTimestamp, interval (7 - dayofweek(myTimestamp)) + ((week(mytimestamp) % 2) * 7) day) as endoffortnight
  from myTable
  group by yr, fortnight
  order by yr, fortnight;

I hope that works or is close enough. I didn't test it with much data.

---

SELECT CEILING(DATEPART(wk,Timestamp)/2.0) AS 'Fortnight'
FROM mytable
GROUP BY CEILING(DATEPART(wk,Timestamp)/2.0)
ORDER BY CEILING(DATEPART(wk,DateKey)/2.0);

Using DATEPART function, wk gives week number of the year.

When we GROUP BY DATEPART(wk,Timestamp)/2, we are grouping by fortnightly.

For more accurate answer one can use CEILING() or ROUND().

FYI DATEPART(wk,Timestamp)/2 range is from 0 to 26 therefore
DATEPART(DATEPART(wk,Timestamp)/2) range is from 0 to 26 but CEILING(DATEPART(wk,Timestamp)/2.0) range is 1 to 27

---

Measure the number of days from an arbitrary starting date, then divide by 14. The following is an implementation in Redshift PSQL:

SELECT COUNT(1)
  ,DATEDIFF('day', '5-28-2012', hour)/14 as fortnight,
  ,DATEADD('day', 14 * (DATEDIFF('day', '5-28-2012', hour)/14), '5-28-2012') as start_of_fortnight
GROUP BY 1
ORDER BY 1

The week() function can get quite complex -- I'd avoid it.

---

I couldn't find a good solution for this online anywhere so I thought I would share mine.

Basically what I was trying to do was write a query which returned a breakdown of fortnightly costs for a business, where the pay week was between Thursday's.

Previous solutions I have seen to this in the work place is generation of a table which stores the fortnight number as well as the date range of that fortnight, allowing users to left join the fortnight information onto the base table.

I didn't want to create a table for this, so I wrote the below query:

SELECT
ROUND(SUM(a.logged/60),2) as logged,
DATE_ADD(DATE_ADD(CONCAT(YEAR(a.start),'-01-01'),INTERVAL (2+a.ft) WEEK), INTERVAL (3-WEEKDAY(CONCAT(YEAR(a.start),'-01-01'))) DAY) as fortnight_end

FROM (
    SELECT
        TIMESTAMPDIFF(MINUTE, a.start, a.finish) as logged,
        a.start,
        CASE WHEN week(a.start)%2=1 AND weekday(a.start) IN (5,4,3) THEN week(a.start)
            WHEN week(a.start)%2=1 AND weekday(a.start) IN (0,1,2,6) THEN week(a.start)-2
            WHEN week(a.start)%2=0 THEN week(a.start)-1
            ELSE week(a.start)
            END AS fortnight    

        FROM table_name as a

) as a

GROUP BY a.fortnight

ORDER BY end desc

The inside query assigns a fortnight number based on a date in the base table. Here it is designed to work for fortnights starting on Thursdays and ending on Wednesdays. To change the start/end days of the fortnight you just need to adjust the WEEKDAY index's inside the IN () clauses of the CASE statements.

The outer query then uses the fortnight number to determine the final date of the fortnight. To change the fortnight end day from Thursday to another day you just need to change the number 3 where it says "3-WEEKDAY". Note that this date is excluded from the group by.

It is also important to note that the above code is designed to sync with the business's pay cycle, which in this case turned out to cycle on odd weeks that were returned by the WEEK function. If your cycle is to start on even weeks, change the %1's to %0's and vice versa.

I hope that this helps someone who is in a similar position to what I was, I was surprised to see no solutions of this form online anywhere.