Search code examples
phpmysqlunix-timestamp

UNIX_TIMESTAMP with date in MySQL query not working

I have dates stored as a varchar in a MySQL database. I know this isn't ideal.. but it what's I have at the moment.

Example date:

$start_date = "11/22/2019";
$start_date = strtotime($start_date);
$end_date = "11/29/2020";
$end_date = strtotime($end_date);

I'm trying to write a query that checks to see if a date in this format falls within a range

I'm running into problems when the start date has the same month as the end date but a different year.

if (!empty($start_date)) {
    $where_clause[] = "UNIX_TIMESTAMP(start_date) >= '" . $start_date . "'";
}

if (!empty($end_date)) {
    $where_clause[] = "UNIX_TIMESTAMP(end_date) <= '" . $end_date . "'";
}

I read that UNIX_TIMESTAMP would allow me to do this but it doesn't seem to be working.

How can I make this query work?

Solution

  • Perhaps the easiest thing to do here would be to just compare your MySQL text dates using STR_TO_DATE against proper ISO date literals in your PHP code. That is, use a query along these lines:

    SELECT *
FROM yourTable
WHERE STR_TO_DATE(start_date, '%m/%d/%Y') > ?;

    To the ? placeholder you would bind a value such as date('Y-m-d', strtotime('11/22/2019')) and not '11/22/2019', the latter which is in a non usable non-ISO format.

    As you mentioned at the start of your question, it would be best to store all dates as bona fide date type column types, rather than text.