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
whn | pages |
2005-06-11 | 13368 |
2005-06-12 | 8129 |
2005-06-13 | 44043 |
… | |
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
whn | pages |
1622 | 13368 |
1623 | 8129 |
1624 | 44043 |
… | |
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.
Next: Modular Arithmetic >>
More SQL Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.
|
|
