SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Working out days elapsed

    Hi Guys,

    in mysql i have 2 files (date files)

    they are:

    1)2007-08-27 13:20:03
    2)August 13, 2007, 10:02 pm

    they are displayed differently, is there a way i can work out the days elapsed between them like this? or do the time stamps need to be the same?

    thanks for any help guys

    Graham

  2. #2
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    strtotime will help you out here.

    PHP Code:
    $time1 strtotime("2007-08-27 13:20:03");
    $time2 strtotime("August 13, 2007, 10:02 pm");

    // Returns the elapsed time in seconds.
    $elapsed $time2 $time1
    This will return a negative difference because I have left the dates in the order you listed them. You can use max and min (or just abs) if you want the difference to be always positive.

    Also, note that it returns the difference in seconds. You will need to divide the result by the number of seconds in one day (60 * 60 * 24 = 86400) to get the difference in days.
    PHP Code:
    $elapsed = ($time2 $time1) / 86400
    Last edited by trib4lmaniac; Aug 27, 2007 at 05:43. Reason: Thought I'd be kind and give example code.

  3. #3
    SitePoint Enthusiast SilkySmooth's Avatar
    Join Date
    May 2003
    Location
    In the PHP Engine :-)
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Graham,

    You can use the DATEDIFF function to get the difference.

    I am not entirely sure what formats the function will accept, but if you have problems with the second of your date formats you should be able to use the DAY(), MONTH(), YEAR() functions to convert it into the right format before passing it to the DATEDIFF() function.

    HTH
    ---------------------
    -- SilkySmooth --
    ---------------------
    Directory Share | Free phpLD Mods

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by graham23s View Post
    in mysql i have 2 files (date files)
    mysql has tables, columns, and rows

    (okay it has files too, but these are used by the mysql software only)

    are you saying you have two columns in a table?

    what datatype are they? DATE? DATETIME? INTEGER? VARCHAR?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Location
    edge of nowhere
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If all else fails you can reconstruct the timestamp from mktime() applied to various substrings of your fields.
    Programming boils down to three things: fast, good and cheap.
    Please pick two.

  6. #6
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys,

    i went back and made the fields the exact same for easiness lol

    2007-08-27 15:22:18
    2007-08-13 22:02:28

    can the days elapsed be easier to work out now?

    thanks guys

    Graham

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by graham23s View Post
    i went back and made the fields the exact same for easiness lol
    but what are their datatypes lol?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh sorry they are both now DATETIME fields.

    Graham

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    excellent, now you can use the mysql DATEDIFF function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks mate i got it lol

    Graham


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
  •