Date arithmetics

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)

Dates may be subtracted. The answer is in units of days.

DATE and TIME Operations

Date and time types

Format a date and time

Specify a date

Match a range of dates

Subtract dates

INTERVAL of time

Components of date

Date functions

Date arithmetics

Select the oldest person

Select a record with the latest date

YYYYMMDD date format

Getting the latest record