SQL Date Handling and Data Trends

Before you can isolate these trends, you must turn the dates into integers to more easily put them into the appropriate buckets. You can pick an arbitrary date and start counting from there. Table 4-2 shows some source data.

Table 4-2. Page views by date  

In Table 4-3, I’ve chosen the first day of the millennium, Monday, January 1, 2001, as day zero. Every date must be converted into the number of days since then. The mechanism for converting to integers is different on different engines. In MySQL, you can create this view using theTO_DAYSfunction:

  CREATE VIEW webalizer2 AS
    SELECT TO_DAYS(whn)-TO_DAYS(DATE '2001-01-01') whn, pages
      FROM webalizer;

Table 4-3. Converting dates to integers

With dates now represented by integers, you can perform arithmetic on them. Taking the modulus 7 value gives you the day of the week. Because 2001-01-01 was a Monday, you will get 0 on every seventh day from then. Tuesday will give you 1, Wednesday 2, and so on, with Sunday having the value 6.