SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Shaydez's Avatar
    Join Date
    Jul 2006
    Location
    Boca Raton, Florida
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Unix TimeStamp SQL

    So i recently converted my database to use strictly UNIX TIMESTAMP when recording traffic or anything in the database

    so i get this 1326988299 I know how to convert it back to readable date through PHP or even through SQL; however i'm not sure how to search by date. For example if i wanted to run a report for everything on Jan 15 2012 (1-15-2012) i'm kind of stuck on that.

    Can anyone help me out.

    Thanks!
    Sr. Website Developer and Internet Marketing
    www.CarlosJa.com Note: If anyone
    needs to get ahold of me please feel free to email me through
    my site. Apparently i missed quite a few private messages.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shaydez View Post
    For example if i wanted to run a report for everything on Jan 15 2012 (1-15-2012) ...
    Code:
    SELECT stuff
      FROM tablez
     WHERE somecolumn >= UNIX_TIMESTAMP('2012-01-15')
       AND somecolumn  < UNIX_TIMESTAMP('2012-01-16')
    this not only reads well, but it just happens to be the most efficient SQL possible, assuming your table has an index on somecolumn which i assume is integer

    let me know if the january 15/16 boundaries for the range test didn't make sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2011
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can convert a timestamp back to a readable date by using PHP's date function:

    PHP Code:
    $timestamp 1326988299;
    $readable date("Y-m-d H:i:s"$timestamp);

    var_dump($readable); 
    above code outputs:

    string(19) "2012-01-19 09:51:39"


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
  •