[RESOLVED] SELECT query by timestamp year help

Sonic_Rage

I'm trying to select records by the year from a mktime() stamp in the database. I thought I could do it this way:

   $new_date = "2006";
   $query = "select date, DATE_FORMAT(date,'%Y') AS year from articles where year = $new_date";

But I'm doing something wrong and haven't been able to figure out what it is. 2 hours of reading documentation and I'm missing something.

Can someone help me out with this query? Thanks.

NogDog

What is the data type of the date column?

rincewind456

Providing that your date column is a data type of date , then your query should work. But this I think is simpler

$new_date = "2006"; 
   $query = "SELECT date from articles where YEAR(date) = $new_date";

Sonic_Rage

The 'date' column is using a unixtimestamp.

rincewind456, I tried your query too and still not working. I'm thinking it has to do with converting the unixtimestamp to something else first?

rincewind456

Then you would need to do something like this

SELECT date FROM articles WHERE YEAR(FROM_UNIXTIME(date))= $new_date

Sonic_Rage

rincewind456, that worked, thank you for your help on this.

rincewind456

No problem. Don't forget to mark it resolved.

Sonic_Rage

done.

bradgrafelman

Note that it would have been simpler had you stored the date using a DATE (or DATETIME) column and that storing a Unix timestamp requires an additional function to be used.

rincewind456

Except that if you use DATETIME instead of TIMESTAMP you require double the amount of storage space. If you dont require the full time (i.e hours, minutes, seconds) then DATE would be preferable as it requires 25% less storage space than TIMESTAMP and 62.5% less than DATETIME.