Helps in date formatting from the database

Question

I am finishing a data filtering system coming from the Database, using BETWEEN, my difficulty is in formatting the field 'date' to timestamp to do the filtering, follow my code:

$where =  "date BETWEEN $start_timestamp AND $end_timestamp";
$this->db->where($where);
$plannings = $this->db->get_where('plannings', array('teacher_id' => 
$teacher_id, 'status' => '1'))->result_array();
foreach ($plannings as $row)

The variables $start_timestamp and $start_timestamp, are already in timestamp format, I tried this way and got the following result:

$where =  "strtotime(date) BETWEEN $start_timestamp AND $end_timestamp";

Upshot:

I was able to solve it this way:

$dataInicial = date('Y-d-m', $start_timestamp);
$dataFinal = date('Y-d-m', $end_timestamp);
$where =  "date BETWEEN '$dataInicial' AND '$dataFinal'";
$this->db->where($where);
$plannings = $this->db->get_where('plannings', array('teacher_id' => 
$teacher_id, 'status' => '1'))->result_array();
foreach ($plannings as $row)

Hence another problem arose, the date is saved as 'Y-d-m', so far all well, but if the day and the month is less than 10 (from 01 to 09), it is saved without zero!

Then it returns the correct data with data from other months or days, I believe I should create a replace, like this below, in the variables $stardate and $stardate;

$year . '-' . ($day < 10 ? str_replace('0', '', $day) : $day) . '-' . ($month < 10 ? str_replace('0', '', $month) : $month),

However, as mentioned above it does not work!

Answer 1

Friend, if I understand correctly, in your bank the field date is storing the values in the format yyyy-dd-mm, and in addition, for the months between 01 till 09 it does not save the zero, and you want to convert this to timestamp to be able to use in your condition BETWEEN, correct?

Well, first you have to treat that field of yours date in select for it to stay as a normal date, that is, for months smaller than 10 have to come to zero and in format 'Y-M-D', this can be done using the function STR_TO_DATE:

STR_TO_DATE(date,'%Y-%d-%m')

Now your date field will behave like a field in the DATE format correctly.
For timestamp fields, you can now convert them to DATE as well, so:

from_unixtime($start_timestamp, '%Y-%m-%d')

Now putting it all together, your code would look like this:

$where =  "STR_TO_DATE(date,'%Y-%d-%m') between from_unixtime($start_timestamp, '%Y-%m-%d') AND from_unixtime($end_timestamp, '%Y-%m-%d')";

You can test an example in this website.