Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 09-05-2009
Member
 
Join Date: May 2008
Posts: 548
Criteria with timestamp in MySQL

I am unable to write a SQL query for :

Select any table where the year is 2009 and the month is $array [x]

I know this is not too complicated, but the thing is that the only search field that I have are timestamp.

My code:

Code:
   include '../../includes/connect_inc.php' ; 
   $ year_and_time = mktime (0, 0, 0, 0, 0, date (Y)); 
   $ year_on = date (Y) +1; 
   $ year_next = mktime (0, 0, 0, 0, 0, $ year_on); 

   $ query = mysql_query ("SELECT COUNT (*) AS rdv woof_agenda FROM WHERE timestamp>". $ year_and_time. "AND timestamp <". $ year_next. "AND ?"); 
   $ return = mysql_fetch_array ($ query) OR die (mysql_error ());
Is this correct? Can I do so?
Reply With Quote
  #2  
Old 09-05-2009
Member
 
Join Date: May 2008
Posts: 685
Re: Criteria with timestamp in MySQL

Why don't you extract the year and month of your date? In fact it will probably be easier in your case. You just need to use the EXTRACT Function.

Syntax:
EXTRACT(unit FROM date)

Description:

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

Example:

mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
-> 2009

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
-> 200907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
-> 20102

mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
-> 123
Reply With Quote
  #3  
Old 09-05-2009
Member
 
Join Date: May 2008
Posts: 548
Re: Criteria with timestamp in MySQL

I still have a problem. The command seem to be correct, but I always return 0.

Code:
  // I made a loop to generate the results by month 
    FOR ( $i = 0 ; $i < 12 ; $i++ )
    { 
   $month = sprintf ( "%02d" ,$i + 1 ) ;
   $year_and_time = date (Y); 

  // To check 
  echo "SELECT COUNT(*) AS nbr FROM woof_agenda WHERE EXTRACT(YEAR FROM timestamp) = " .$year_and_time. " AND EXTRACT(MONTH FROM timestamp) = " .$month. "<br />" ;
	  
   $query = mysql_query ( "SELECT COUNT(*) AS nbr FROM woof_agenda WHERE EXTRACT(YEAR FROM timestamp) = " .$year_and_time. " AND EXTRACT(MONTH FROM timestamp) = " .$month. "" ) ;
   $return = mysql_fetch_array ($query); 
   echo $return ['nbr'];  
  }
Reply With Quote
  #4  
Old 09-05-2009
Member
 
Join Date: May 2008
Posts: 685
Re: Criteria with timestamp in MySQL

Without the structure of your table and an extract of what it contains, it will be difficult to help you. However you can do something like this:

Code:
WHERE EXTRACT (YEAR FROM timestamp ) = ".$year_and_time." AND EXTRACT ( MONTH FROM timestamp ) = ".$month_and_time."
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Criteria with timestamp in MySQL"
Thread Thread Starter Forum Replies Last Post
Unable to install MySQL and MySQL Workbench on Linux mint 10 Zared Windows Software 4 13-01-2011 01:08 AM
What is Timestamp in SQL? Shekhar12 Software Development 4 30-11-2009 10:02 AM
Mysql Error : Can't connect to local mysql server through socket ' var lib mysql mysql.sock' 2 roshan45 Software Development 3 07-11-2009 09:36 PM
How to convert UNIX timestamp to MySQL Warner Software Development 2 16-05-2009 08:20 PM
How to convert MYSQL into Readable PHP timestamp Aanand Software Development 2 15-05-2009 11:26 PM


All times are GMT +5.5. The time now is 08:15 PM.