CODEWITHSUNDEEP
phpmysql
Babu
Babu

Reputation: 455

Get data from mysql table which column type is " timestamp "?

In mysql database I've a date value which data type is timestamp. It's showing this value is something like this : 1415607900, 1394439900, 1412929500 etc.

Now In Html form I can pickup a date. Date value is m/d/y format and this date is for searching data to DB. I can pickup Date From or Date To or Both from this html form field.

Date from is searching data from pickup date to current date.
Date To is searching all data until picktup Date.
If both date value exsit then it's searching data between this 2 dates.

So how do I search data against this timestamp ?

Please check my following mysql query which I'm running but can't get right result. I'm highly appreciate your suggestions/solutions. Thank You. 

if(!empty($ad_datefrom) && empty($ad_dateto)){    
    $getSearch .= "AND cd.created_date2 BETWEEN '$ad_datefrom' AND '$date' ";   
}

if(!empty($ad_dateto) && empty($ad_datefrom)){    
    $getSearch .= "AND cd.created_date2 BETWEEN '0000-00-00' AND '$ad_dateto'"; 
}

if(!empty($ad_datefrom) && !empty($ad_dateto)){
    $getSearch .= "AND cd.created_date2 BETWEEN '$ad_datefrom' AND '$ad_dateto' ";  
}

----------------------------------------------------------------------------------
cd = contact_details Table,  
created_date2 = date column (data type : timestamp)  
$date = current date which format is m/d/y

Update : 

/* this is for Date from*/
if(!empty($ad_datefrom) && empty($ad_dateto)){    
        $getSearch .= "AND cd.created_date BETWEEN UNIX_TIMESTAMP('$ad_datefrom') AND UNIX_TIMESTAMP('$date') ";    
}

/* this is for Date To*/
if(!empty($ad_dateto) && empty($ad_datefrom)){    
    $getSearch .= "AND cd.created_date BETWEEN UNIX_TIMESTAMP('0000-00-00') AND UNIX_TIMESTAMP('$ad_dateto') "; 
}

/* this is for Date from and Date To*/
if(!empty($ad_datefrom) && !empty($ad_dateto)){
    $getSearch .= "AND cd.created_date BETWEEN UNIX_TIMESTAMP('$ad_datefrom') AND UNIX_TIMESTAMP('$ad_dateto')";    
}

Upvotes: 0

Views: 112

Answers (1)

Jaylen
Jaylen

Reputation: 40349

You can use FROM_UNIXTIME() to convert Unix time into date time.

Here is an example SELECT * FROM tbl where FROM_UNIXTIME(dt) between '2014-10-01 00:00:00' and "2014-11-01 00:00:00'

Upvotes: 1

Related Questions