Date functions

Group by day of the week (using date functions)

schema:gisq

We might want to count, or find the average by days of the week. This approach uses the date functions.

In this example we note that top of the pops usually goes out on a Thursday or Friday.

SELECT TO_CHAR(wk,'D'), COUNT(song)
  FROM gisq.totp
 GROUP BY TO_CHAR(wk,'D')

SELECT DAYOFWEEK(wk), COUNT(song)
  FROM totp
 GROUP BY DAYOFWEEK(wk)

DAYOFWEEK gives 1 for Sunday, 2 for Monday,... 7 for Saturday.

An alternative is to use WEEKDAY which gives 0 for Monday, ... 6 for Sunday.

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