Page 1 of 1

Unix time stamap

Posted: Wed Mar 18, 2009 2:06 pm
by ihabgr
I have a table called "donation "with field "amount" and another field "r-tdate" which is include datestamp like 1233712799
and i want to run the query
$donquery= 'SELECT COUNT(*) FROM `Donation`where UNIXTIME(MONTH(r_tdate) =1 AND YEAR(r_tdate) = 2008 )';
but it doesn't work because of the date format
so please help me

Re: Unix time stamap

Posted: Wed Mar 18, 2009 2:10 pm
by pickle
UNIXTIME converts MySQL timestamps to UNIX timestamps. You want to do the reverse with FROM_UNIXTIME:

Code: Select all

SELECT
  COUNT(*)
FROM
  `Donation`
WHERE
  MONTH(FROM_UNIXTIME(`r_tdate`)) =1 AND
  YEAR(FROM_UNIXTIME(`r_tdate`)) = 2008
If you're going to be doing lots of these types of queries, you might want to consider changing the date format from a unix timestamp, to a native MySQL datetime stamp - to cut down on the complexity of the queries.

Re: Unix time stamap

Posted: Wed Mar 18, 2009 2:18 pm
by ihabgr
I tryed and i didnt work

$donquery= 'SELECT COUNT(*) FROM `Donation`where MONTH(FROM_UNIXTIME(`r_tdate`)) =1 AND AYEAR(FROM_UNIXTIME(`r_tdate`)) = 2008';
$donresult = mysql_query($donquery) ;
$doncom = mysql_result($donresult, 0);
$donquery1 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 2 AND YEAR(r_time) = 2008 )';
$donresult1 = mysql_query($donquery1) ;
$doncom1 = mysql_result($donresult1, 0);
$donquery2 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 3 AND YEAR(r_time) = 2008 )';
$donresult2 = mysql_query($donquery2) ;
$doncom2 = mysql_result($donresult2, 0);
$donquery3 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 4 AND YEAR(r_time) = 2008 )';
$donresult3 = mysql_query($donquery3) ;
$doncom3 = mysql_result($donresult3, 0);
$donquery4 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 5 AND YEAR(r_time) = 2008 )';
$donresult4 = mysql_query($donquery4) ;
$doncom4 = mysql_result($donresult4, 0);
$donquery5 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 6 AND YEAR(r_time) = 2008 )';
$donresult5 = mysql_query($donquery5) ;
$doncom5 = mysql_result($donresult5, 0);
$donquery6 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 7 AND YEAR(r_time) = 2008 )';
$donresult6 = mysql_query($donquery6) ;
$doncom6 = mysql_result($donresult6, 0);
$donquery7 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 8 AND YEAR(r_time) = 2008 )';
$donresult7 = mysql_query($donquery7) ;
$doncom7 = mysql_result($donresult7, 0);
$donquery8 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 9 AND YEAR(r_time) = 2008 )';
$donresult8 = mysql_query($donquery8) ;
$doncom8 = mysql_result($donresult8, 0);
$donquery9 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 10 AND YEAR(r_time) = 2008 )';
$donresult9 = mysql_query($donquery9) ;
$doncom9 = mysql_result($donresult9, 0);
$donquery10 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 11 AND YEAR(r_time) = 2008 )';
$donresult10 = mysql_query($donquery10) ;
$doncom10 = mysql_result($donresult10, 0);
$donquery11 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 12 AND YEAR(r_time) = 2008 )';
$donresult11 = mysql_query($donquery11) ;
$doncom11 = mysql_result($donresult11, 0);
$donquery12 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 1 AND YEAR(r_time) = 2009 )';
$donresult12 = mysql_query($donquery12) ;
$doncom12 = mysql_result($donresult12, 0);
$donquery13 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 2 AND YEAR(r_time) = 2009 )';
$donresult13 = mysql_query($donquery13) ;
$doncom13 = mysql_result($donresult13, 0);
$donquery14 = 'SELECT COUNT(*) FROM `Donation`where (MONTH(r_time) = 03 AND YEAR(r_time) = 2009 )';
$donresult14 = mysql_query($donquery14) ;
$doncom14 = mysql_result($donresult14, 0);



?>

<p align="center"><strong>Total Job ads </strong></p>
<table width="100%" border="1" cellpadding="2" cellspacing="2" bordercolor="#9999FF">
<tr>
<td bordercolor="#9999FF" bgcolor="#9999FF"><strong>Jan. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Feb. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Mar. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Apr.. 2008</strong></td>
<td bgcolor="#9999FF"><strong>May. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Jun. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Jul. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Aug. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Sep. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Oct. 2008</td>
<td bgcolor="#9999FF"><strong>Nov. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Dec. 2008</strong></td>
<td bgcolor="#9999FF"><strong>Jan. 2009</strong></td>
<td bgcolor="#9999FF"><strong>Feb. 2009</strong></td>
<td bgcolor="#9999FF"><strong>Mar. 2009</strong></td>
</tr>
<tr> <strong>
<td bordercolor="#9999FF"><?echo $doncom;?></td>
<td><?echo $doncom1;?></td>
<td><?echo $doncom2;?></td>
<td><?echo $doncom3;?></td>
<td><?echo $doncom4;?></td>
<td><?echo $doncom5;?></td>
<td><?echo $doncom6;?></td>
<td><?echo $doncom7;?></td>
<td><?echo $doncom8;?></td>
<td><?echo $doncom9;?></td>
<td><?echo $doncom10;?></td>
<td><?echo $doncom11;?></td>
<td><?echo $doncom12;?></td>
<td><?echo $doncom13;?></td>
<td><?echo $doncom14;?></td>
</tr> </strong>
</table>

Re: Unix time stamap

Posted: Wed Mar 18, 2009 2:40 pm
by pickle
Notice how my code looks nice & formatted? That's because I used tags. Please do the same.

How am I supposed to know what the problem is when all you say is "It didn't work"? I'm not trying to be rude here, but really - how am I supposed to help?

I've given you the function you need to use - try & debug it yourself.

Re: Unix time stamap

Posted: Wed Mar 18, 2009 2:54 pm
by ihabgr
It works thank you