Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a mysql table that contains something like:

+-------------+-----------------+
| FOO1        | FOOTIME         |
+-------------+-----------------+
|        blah |   1234567890000 |
+-------------+-----------------+

FOOTIME uses unix timestamp.

I want to run a query that essentially says "how many rows contain foo time older than January 1st 2012?

I thought I'd want to do something like select count(*) from foodb.FOOTABLE where FOOTIME <= TIMESTAMP('2012-01-01'); but that didn't work.

What am I missing?

share|improve this question
    
The function you want is UNIX_TIMESTAMP() –  ypercube Jan 31 at 22:25

1 Answer 1

up vote 1 down vote accepted

Try this:

SQL code:

SELECT COUNT(FOO1)
FROM footable
WHERE FOOTIME >= UNIX_TIMESTAMP('2012-01-01');


SELECT COUNT(FOO1)
FROM footable
WHERE FROM_UNIXTIME(FOOTIME)>='2012-01-01'; 

Sample data:

CREATE TABLE footable (
  FOO1 varchar(30) primary key,
  FOOTIME int(11)
  );

INSERT INTO footable (FOO1, FOOTIME)
VALUES ('blah', UNIX_TIMESTAMP(now() - INTERVAL 1 YEAR)),
('Blahblah', UNIX_TIMESTAMP(now() - INTERVAL 2 YEAR)),
('BLablahblah', UNIX_TIMESTAMP(NOW() - INTERVAL 5 YEAR));

SQLFiddle demo

share|improve this answer
2  
Wouldn't it be more efficient to use WHERE FOOTIME<UNIX_TIMESTAMP('2012-01-01')? That way the conversion function is only called once (instead of being called for each row), and the query could use an index on FOOTIME if one exists. –  Gord Thompson Feb 2 at 17:33
    
@GordThompson I changed the answer. Your query was indeed more efficient. It saved 2 ms in this same example alone. –  Mr. Radical Feb 2 at 18:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.