CODEWITHSUNDEEP
mysqldatabase
Zabs
Zabs

Reputation: 14142

Select rows before date via MySQL

I have the following query:

SELECT from_unixtime(o.date_created, '%Y-%m-%d') AS date_created, op.*
FROM order_product op
WHERE o.date_created > '2009-01-01';

This should run all orders that are AFTER 1st Jan 2009, but when I run if it rows that are BOTH before and after 2009??

What is the mistake I made?

Upvotes: 0

Views: 2425

Answers (3)

Arth
Arth

Reputation: 13110

Just in case anyone stumbles across this and thinks that the accepted answer is the only way to get this to work.

The original comparison will work perfectly well:

 WHERE date_created > '2009-01-01'

Provided that date_created is of type TIMESTAMP and not an INT pretending to be a timestamp.

MySQL is clever enough to be able to convert '2009-01-01' to a TIMESTAMP for the comparison. (As a disclaimer, this may break if the default date format is tampered with, but I'm not convinced).

As a side note, I would also change from_unixtime(date_created, '%Y-%m-%d') to DATE(date_created) as it appears to return NULL otherwise.

Upvotes: 2

Jose Tepedino
Jose Tepedino

Reputation: 1584

It seems you need to check your database's locale configuration, as the default date format might vary from one installation to another (eg: yyyy-mm-dd, mm-dd-yyyy, dd-mm-yyyy).

It could be safer to explicitly define the date format, something like:

WHERE to_char(o.date_created,'yyyy-mm-dd') > '2009-01-01'

or

WHERE FORMAT(o.date_created,'YYYY-MM-DD') > '2009-01-01'

Please use the corresponding date format function available on your database type (mysql).

Upvotes: 0

Dan
Dan

Reputation: 876

Looks to be that o.date_created is unix time, and not in the format you want for the query.

Try

WHERE o.date_created > unix_timestamp('2009-01-01');

Upvotes: 3

Related Questions