[SOLVED] mySql date manipulation help

[drakal30]

I am storing dates in UNIX_TIME in my database, I want to select all fields in a table from the past 14 days, here is my query.

 

$query = "SELECT DATE_SUB(DATE_FORMAT(FROM_UNIXTIME(tDate),'%Y-%m-%d'), INTERVAL 14 DAY) COUNT(id) as numrows FROM tMachIssue";

 

The above query errors out before the COUNT part of the string.  Is my syntax wrong?  Thanks in advance.

[Barand]

<?php
$query = "SELECT COUNT(*) as numrows 
          FROM tMachIssue
          WHERE FROM_UNIXTIME(tDate) > NOW() - INTERVAL 14 DAY"; 

[drakal30]

Awesome worked perfectly.  If you have time could you explain the syntax I am properly betraying my ignorance in complicated SQL queries but what does the > do in (FROM_UNIXTIME(tDate) > NOW() - INTERVAL 14 DAY) I am guessing some kind of pointer?

[Barand]

FROM_UNIXTIME(tDate) converts the unix time to YYYY-MM-DD

 

NOW() give current date

NOW() - INTERVAL 14 DAY gives date 14 days ago

 

> greater than

 

 

ie

WHERE the date is greater than the date 14 days ago

[drakal30]

Oh Okay, that is simple but elegant.  I thank you for your time. Makes sense to me now.