CODEWITHSUNDEEP
mysqldate
Yuseferi
Yuseferi

Reputation: 8670

unusual result in Mysql Date Comparison

I have a datetime datatype in my table, 

 `DateAdded` datetime(4) DEFAULT NULL,

I have a record in my database with DateAdded = 2017-09-11 17:02:48.6531 value, ( it's ID = 16452994 ).

When I want to get it with following query return NULL

select `ID`,`DateAdded` from `Add` where   `DateAdded` <= FROM_UNIXTIME(('1505071799' +86400 ), '%Y-%m-%d %h:%i:%s') and ID =16452994 ;

FYI : FROM_UNIXTIME(('1505071799' +86400 ), '%Y-%m-%d %h:%i:%s') = 2017-09-11 23:59:59

It's strange that 2017-09-11 17:02:48.6531 <= 2017-09-11 23:59:59 return false

but when I try the following query I'll get my desire result.

select `ID`,`DateAdded` from `Add` where   `DateAdded` <= FROM_UNIXTIME(('1505071799' +86400 +1 ), '%Y-%m-%d %h:%i:%s') and ID =1645299;

I want to know why this is happening and how can I resolve this problem?

Upvotes: 0

Views: 28

Answers (1)

Pred
Pred

Reputation: 9042

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.

If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT() function.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

Upvotes: 1

Related Questions