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?


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

to tell us what the query does that isn’t “fine”

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
;

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

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