Geek asks and answers

Grouping mysql records every two weeks

I want to group records in a timestamp field overnight. I do this weekly, but I want it to be two weeks. How should I do it? Is there a specific way to do this, for example, how do we group by week using WEEK (function "timestamp", "% d-% m-% Y")?

In the image below you can see dates 21 and 14 for two weeks. 10. Any suggestions?

+3
source share
4 answers

week() 2, ( ). - 1 ( week()), , , , 1 2 1, 0 , ceil():

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

: week()

: ...

. .

(, , , ), , , , , - , .

, , , 7 , , . ( .) . , - ( MySQL default_week_format week(), ).

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;

, . .

+5
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);

DATEPART , wk .

GROUP BY DATEPART(wk,Timestamp)/2, .

CEILING() ROUND().

FYI DATEPART (wk, Timestamp)/2 0 26,
DATEPART (DATEPART (wk, Timestamp)/2) 0 26 (DATEPART (wk, Timestamp)/2.0) 1 27

+1

, 14. 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

week() - .

0

-, .

, , , , .

, , - , , , .

, :

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

. , . , WEEKDAY IN() CASE.

. , 3, "3-". , .

, , , WEEK. , % 1 % 0 .

, -, , , , .

0

More articles:


All Articles