Home arrow SQL arrow Page 2 - SQL Date Handling and Data Trends
SQL

SQL Date Handling and Data Trends


In this second part of a four-part series explaining how SQL handles date calculations, you will learn how to uncover trends in your data. This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Author Info:
By: O'Reilly Media
Rating: 5 stars5 stars5 stars5 stars5 stars / 6
December 13, 2007
TABLE OF CONTENTS:
  1. · SQL Date Handling and Data Trends
  2. · Turning the Dates into Integers
  3. · Modular Arithmetic
  4. · Changes for SQL Server, Access, and Oracle

print this article
SEARCH DEVARTICLES

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.


blog comments powered by Disqus
SQL ARTICLES

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials