1

I have varchar field in database and format of that field is like '10 Feb 2016 08:26 PM'.

Now I want to fetch upcoming data, So how can i get that ?

$ctime=strtotime(date("Y-m-d H:i:s"));

if($type=='1'){
    $books = $objData->getAll("select * 
                               from bookings 
                               where custID='".$custID."' 
                                 and fromTime>'".$ctime."'");
}

But I am not getting correct result, please help.

Himanshu Upadhyay
  • 723
  • 7
  • 30
  • 1
    Of course you are not getting the right reslut because you are comparing varchars. Why don't you use the valid type "datetime" for datetime? – B001ᛦ Sep 06 '16 at 14:09
  • 1
    You should use a valid mysql date format in your database and only format it for the user when you display the information in the browser. – jeroen Sep 06 '16 at 14:09
  • 1
    When working with dates and times, cast to date time formats. Do not compare based on string data. You don't store numbers you ever want to do math on as Varchar why store dates as varchar? – xQbert Sep 06 '16 at 14:10
  • 1
    Agreeing with all above. If you know PHP, write a script to harvest the date info char entries and then INSERT into a new column in the db. Good luck. – user3741598 Sep 06 '16 at 14:14
  • 1
    You should read about, understand and start using parameterized queries before bobby tables comes to visit. – Sean Lange Sep 06 '16 at 14:26
  • Look at the answer I accepted, This is what I wanted, Its okay to change column format to datetime in database, but at some scenario you have to deal with developed earlier. – Himanshu Upadhyay Sep 07 '16 at 05:26

1 Answers1

3

First of all, as mentioned in comments you should use proper types for dates. To answer your question, it is still possible to achieve, using STR_TO_DATE mysql function.

$objData->getAll("select * from bookings where custID=".$custID."'
AND unix_timestamp(STR_TO_DATE(fromTime, '%d %b %Y %h:%i %p')) > ".time());

Link: Convert VARCHAR timestamp to TIMESTAMP?

Community
  • 1
  • 1
Alexey Chuhrov
  • 1,787
  • 12
  • 25
  • Its working, but in 1 scenario (if time has 00 in hour or minute, for ex- 19 sep 2016 00:10 AM) it returns NULL, if i try to fetch unix_timestamp(STR_TO_DATE(fromTime, '%d %b %Y %h:%i %p')) – Himanshu Upadhyay Sep 20 '16 at 05:39
  • If we are using am/pm it should be 12h format `12:10 AM`. Here's 24h somehow. Minutes should be fine. Here's the link with all formats: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format. If this is the only case of mismatch maybe this would work: `"select * from bookings where custID=".$custID."' AND unix_timestamp(STR_TO_DATE(REPLACE(fromTime, '00:', '12:'), '%d %b %Y %h:%i %p')) > ".time()` – Alexey Chuhrov Sep 20 '16 at 08:34