Date arithmetics

From SQLZOO
Revision as of 15:17, 17 July 2012 by Marek (Talk | contribs) (Created page with "Group by day of the week (using arithmetic). <div class='ht'> <div class=params>schema:gisq</div> <div> We can use modular arithmetic to calculate the day of the week. We hap...")

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Group by day of the week (using arithmetic).

schema:gisq

We can use modular arithmetic to calculate the day of the week.

We happen to know that 20 May 1962 was a Sunday. We calculate the number of days from that day and take mod 7 value. This tells us the day of the week: 0 is Sunday, 1 is Monday...

 
 
 
SELECT MOD(wk - TO_DATE('1962-05-20','YYYY-MM-DD'),7),
       COUNT(song)
  FROM gisq.totp
GROUP BY MOD(wk - TO_DATE('1962-05-20','YYYY-MM-DD'),7)