MYSQL Query for last twelve months of records

UNIX for Beginners Q & A
1

Hi I am trying to run a MYSQL query to display all records within a 12 month period. I have to use from_unixtime function to great a date object.

select call_start_time from call_detail_records
 where call_start_time IN (
   select from_unixtime(call_start_time) from call_detail_records
    where from_unixtime(call_start_time) < Now()
    and from_unixtime(call_start_time) < DATE_ADD(Now(),INTERVAL- 12 MONTH)
   );

The query is syntactically correct but mysql returns no records when there are some. Can someone help?

2

You want to get only the last 12-months' records?

Shouldn't:

from_unixtime(call_start_time) < DATE_ADD(Now(),INTERVAL- 12 MONTH) )

Be:

from_unixtime(call_start_time) > DATE_ADD(Now(),INTERVAL- 12 MONTH) )

Is the first part even necessary?

from_unixtime(call_start_time) < Now()

I would expect everything in the table is older than "now".

3

Also you don't have to use a nested query, you can simply run:-

SELECT call_start_time 
FROM   call_detail_records 
WHERE From_unixtime(call_start_time) > Date_add(Now(), interval - 12 MONTH) 
;
4

Of course, good point :slight_smile: