SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trying to get year from a mysql timestamp.

    Hello people,

    I'm a bit rusty and need your assistance.

    The following query is fine until I add the AND condition to get records that have the Time_Into_Site of 2012.

    what am I forgetting?

    Code MySQL:
    SELECT 
      COUNT( * ) 
    , session_ID
    , Time_Into_Site
    FROM statistics 
    WHERE Domain_Viewed =  'domain.com'
    and YEAR(Time_Into_Site) = 2012
    GROUP BY session_ID
    ORDER BY Time_Into_Site DESC

    Oops, I forgot to add that Time_Into_Site is a mysql timestamp
    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    what am I forgetting?
    to tell us what the query does that isn't "fine"
    Code:
    CREATE TABLE test_timestamp
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , ts TIMESTAMP
    );
    INSERT INTO test_timestamp (ts) VALUES
     ( '2011-04-20' )
    ,( '2011-04-20 04:20' )
    ,( CURRENT_DATE )
    ,( CURRENT_TIMESTAMP )
    ;
    
    SELECT * 
      FROM test_timestamp
     WHERE YEAR(ts) = 2012
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    SELECT COUNT( * ) , session_ID, Time_Into_Site
    FROM  `statistics` 
    WHERE Domain_Viewed =  'shellevenhouse.com'
    AND DATE_FORMAT( Time_Into_Site,  '%Y' ) =  '2011'
    GROUP BY session_ID
    ORDER BY Time_Into_Site DESC

    This seems to do the trick. I omitted to say that the previous query return zero results.

    Thanks rudy, I think I get the gist of your post: that the datatype might be better stored as DATE with time etc in other cols.

    bazz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    Thanks rudy, I think I get the gist of your post: that the datatype might be better stored as DATE with time etc in other cols.
    oh my dear goodness no, that is the furthest thing from my gist

    i would (almost) ~never~ advocate splitting the time off into a separate column

    my gist, if you bothered to run my sample, was that extracting the year from a TIMESTAMP column using the YEAR function ~does~ work properly

    your DATE_FORMAT approach also works (but is less elegant and likely a picosecond slower because it requires conversion of the timestamp value to a string for a string comparison)

    so now the only other differences between the two queries you posted was that the one you said returned zero rows went after "domain.com" for 2012 while the one you said worked went after 2011 for a different domain

    at this point i'm inclined to think that there were no 2012 rows to return for "domain.com"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •