UNIX_TIMESTAMP with date in MySQL query not working

Question

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?

I think the date should be formatted as 2019-11-22 (yyyy-mm-dd) then pass it as parameters of UNIX_TIMESTAMP. It doesn't accept any date format unless you convert it. w3resource.com/mysql/date-and-time-functions/… — Roshan
– Roshan, Commented Feb 4, 2019 at 6:06

Salman Arshad · Accepted Answer · 2019-02-04 06:36:16Z

2

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.

edited Feb 4, 2019 at 6:36

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

answered Feb 4, 2019 at 6:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

UNIX_TIMESTAMP with date in MySQL query not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related