Codeigniter And MYSQL DateTime

Question

I Have a query as follows:

$query = $this->db->get_where('Bookings', array('Status' => 0, 'Driver_ID' => null, 'Date'=> 'NOW()'));

The Date field is a datetime type, and I want to be garbing records where their date is the same as today, however the above, and everything else I have tired does not work.

Can anyone one show me how to correctly pull records that date it today, ignoring the time part of datetime.

Thanks for any help

UPDATE

I Have now converted the query to the following

$start = date('Y-m-d 00:00:00');
    $end = date('Y-m-d 23:59:59');
    $query = $this->db->get_where('Bookings', array('Status' => 0, 'Driver_ID' => null, 'Date' => 'BETWEEN '.$start.' AND '.$end));

However still no luck, just retuning no results!

danneth · Accepted Answer · 2012-04-17 14:51:23Z

4

I would try

$this->db->where('Status', 0);
$this->db->where('Driver_ID', null);
$this->db->where('DATE(Date)', date('Y-m-d'), FALSE);
$query = $this->db->get('Bookings');

answered Apr 17, 2012 at 14:51

danneth

2,7911 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben_hawk Over a year ago

Confirmed, this does work, but FALSE had to be removed and qoutes need to be added around (date) like so $this->db->where('Status', 0); $this->db->where('Driver_ID', null); $this->db->where('DATE(Booking_Date)', date('Y-m-d')); $query = $this->db->get('Bookings');

allen213 Over a year ago

I think this is a better answer, then in this case as its closer to the functions you were trying to use in the first place

allen213 · Accepted Answer · 2012-04-17 14:54:33Z

2

$params = array('Status' => 0, 'Booking_Date'=> date('Y-m-d'));
$this->db->query('SELECT * FROM Bookings WHERE Status=? AND Driver_ID Is Null AND    Date(Booking_Date) = ?',$params);

Think that will work

edited Apr 17, 2012 at 14:54

answered Apr 17, 2012 at 14:43

allen213

2,2692 gold badges15 silver badges21 bronze badges

2 Comments

Ben_hawk Over a year ago

could you update query to have Booking_Date for referencing my column, I have got confused with the column names and function calls

Ben_hawk Over a year ago

Ah Got it going in the end Thanks very much!

Logan Serman · Accepted Answer · 2012-04-17 13:45:13Z

1

In your code, you are saying you want a DateTime field to be NOW(). The problem is that NOW() gives a DateTime value, that is, a date in the form "YYYY-MM-DD" followed by a time in the form "HH:MM:SS".

What your query is doing is saying "Give me records where the Date is today, at this exact second". What you want is "Give me records where the Date is today".

This is why using DateTime fields in a database is usually cumbersome. You will have to convert your Date field to be just the date, without the time, using the MySQL function DATE(), and instead of NOW() which returns a DateTime value, you will want to use CURDATE() which returns only the Date value. I am not experienced with CodeIgniter specifically, but try:

$query = $this->db->get_where('Bookings', array('Status' => 0, 'Driver_ID' => null, 'DATE(Date)'=> 'CURDATE()'));

(I don't know if you can apply MySQL functions to fields with $this->db->get_where).

answered Apr 17, 2012 at 13:45

Logan Serman

30k28 gold badges107 silver badges144 bronze badges

5 Comments

Ben_hawk Over a year ago

Hi thanks for the responses, but so far neither has worked, trying to fiddle with the answers but just displays nothing so far :(

Logan Serman Over a year ago

Is there a way to see what SQL the get_where function is generating? What if you just ran the query SELECT * FROM Bookings WHERE Status = 0 AND Driver_ID = NULL and DATE(`Date`) = CURDATE()? Perhaps the query is failing because you have a field named "Date", when that is a keyword in MySQL (because of the DATE() function)?

Ben_hawk Over a year ago

Updated name from Date to Booking_Date with no luck, and also tried the manual SQL statement you suggested with no luck. Below is one of the stored dates I have 2012-04-17 13:45:24

Logan Serman Over a year ago

Can you tell me what you are getting for: SELECT CURDATE(), SELECT DATE(Booking_Date) FROM Bookings, and date(Y-m-d).

Logan Serman Over a year ago

Just to be clear date('Y-m-d') is a PHP function, not MySQL (also forgot the quotes around the parameters, too late to edit).

Rooneyl · Accepted Answer · 2012-04-17 13:38:09Z

0

MySql standard datetime format is date("Y-m-d H:i:s").

So you query will need to get everything for the current day, so a where clause someything like;

SELECT * FROM `Bookings` WHERE `Date` BETWEEN date("Y-m-d 00:00:00") AND date("Y-m-d 23:59:59")

answered Apr 17, 2012 at 13:38

Rooneyl

7,9226 gold badges58 silver badges83 bronze badges

2 Comments

Ben_hawk Over a year ago

Hi thanks for the responses, but so far neither has worked, trying to fiddle with the answers but just displays nothing so far :(

Rooneyl Over a year ago

TBH i would do something like @Logan Serman suggested. I would store all the dates as a UNIX timestamp. It makes playing with the values easy as they are just integers.

Collectives™ on Stack Overflow

Codeigniter And MYSQL DateTime

4 Answers 4

2 Comments

2 Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related