CODEWITHSUNDEEP
mysql
Paul Kuipers
Paul Kuipers

Reputation: 3

Why is my AND statement not working in MySQL/PHPMyAdmin

I am trying to retrieve tickets from a database that are created in the last 6 weeks. Therefore i am trying to use an delimiter which filters that period but the database time is based on an UNIX timestamp and i want to search on a logical date.

I've tried date(), timestamp(), CAST() in the last line of my query but most throw errors or don't actually filter the wanted results

SELECT  ticket,
    company,
    title,
    status,
    department,
    user,
    (FROM_UNIXTIME(timestamp,"%d-%m-%Y")) AS 'Start date',
    (FROM_UNIXTIME(close_time,"%d-%m-%Y")) AS 'Close date'
FROM ticket_database
WHERE department IN ('stack_mysql')
**AND DATE_FORMAT(timestamp,'%d-%m-%Y') > '11-02-19'**
;

I expect that all tickets created on or after 11-02-19 are shown but instead it ignored this date and throws everything at me since 2006.

Help is appriciated

Upvotes: 0

Views: 60

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

It is not the best idea to use a function on a field in the WHERE Clause. This will generate a FULL TABLE SCAN. MySQL must read each ROW, run the function and the compare.

Better is to use the function on the constant part of the compare, but here is it is not nesecary 

SELECT  ticket,
    company,
    title,
    status,
    department,
    user,
    (FROM_UNIXTIME(`timestamp`,"%d-%m-%Y")) AS 'Start date',
    (FROM_UNIXTIME(close_time,"%d-%m-%Y")) AS 'Close date'
FROM ticket_database
WHERE department IN ('stack_mysql')
AND `timestamp` > unix_timestamp('2019-02-11 00:00:00');

Note: Make your life easier. Dont use reserved Keywords like timestamp not as fieldnames. if you do that you must quote it in backticks

Upvotes: 1

Nick
Nick

Reputation: 147176

You have a couple of issues. Firstly, based on this line:

(FROM_UNIXTIME(timestamp,"%d-%m-%Y")) AS 'Start date',

the timestamp column is a unix timestamp, not a date and thus can't be passed to DATE_FORMAT. Secondly, 11-02-19 is not a valid MySQL date format and thus can't be compared to a date directly; you need to use STR_TO_DATE to convert that to a date instead. So, to make it work, try this:

AND FROM_UNIXTIME(timestamp) > STR_TO_DATE('11-02-19', '%d-%m-%y')

To allow MySQL to use an index on the timestamp column, remove the FROM_UNIXTIME call and convert the date into a timestamp instead:

AND timestamp > UNIX_TIMESTAMP(STR_TO_DATE('11-02-19', '%d-%m-%y'))

Upvotes: 0

Related Questions