SQL Date Handling and Data Trends - Turning the Dates into Integers (Page 2 of 4 )
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.