SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Auto increment Date field in MySQL

    Hi ,

    I have a table, does not contain a date field in it. It has day of week values 1 to 7. Considering that i retrieve these 7 rows from the table, I want an extra field, say 'test_date' created whose first row value will be a date I want to start with and then auto increment the date value for the next 6 rows. is there a way we can do this?

    Thanks in Advance
    Thank You
    Known is a Drop, Unknown is an Ocean

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    assuming "dw" is the column from your table with the numbers 1 through 7...
    Code:
    SELECT '2010-05-09' + INTERVAL dw DAY AS running_date
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, It is working absolutely fine. What if I would like to extend the requirement like this:

    With the query you provided above, this is what we can get
    1 - 2010-05-10
    2 - 2010-05-11
    3 - 2010-05-12
    4 - 2010-05-13
    5 - 2010-05-14
    6 - 2010-05-15
    7 - 2010-05-16

    What if I want to get the below shown by still having 2010-05-09 as the date I have at hand, and along with that I have
    a value from 1-7 from where I want the date to start incrementing(3 in this case)

    3 - 2010-05-10
    4 - 2010-05-11
    5 - 2010-05-12
    6 - 2010-05-13
    7 - 2010-05-14
    1 - 2010-05-15
    2 - 2010-05-16

    Thanks again
    Thank You
    Known is a Drop, Unknown is an Ocean

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm surprised you even had to ask...

    SELECT '2010-05-09' + INTERVAL dw+1 DAY ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm surprised you even had to ask...

    SELECT '2010-05-09' + INTERVAL dw+1 DAY ...
    New to such queries, so could not understand much. But, the change you said just adds the date given. I had to start with the same date, but corresponding to a value between 1-7 and wrap over the date increment.

    Understood the basic query suggested by you, made some changes to get what i wanted.

    Code:
    SELECT '2010-05-09' + INTERVAL dw-(3-1) DAY AS running_date,dw
      FROM test_table where dw >= '3'
    union
     SELECT '2010-05-09' + INTERVAL dw+(8-3) DAY AS running_date,dw
      FROM test_table where dw < '3' ;
    Here 3 can be anything between 1 and 7, which we have at hand.

    Thanks Again. Problem Solved
    Thank You
    Known is a Drop, Unknown is an Ocean


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •