Take the 2-minute tour ×

I'm trying to select all dates between 2 dates. In the database I have records in unix timestamp format while my date_ini and date_final variables are in date format (00-00-00 00:00:00)

    $msgs = mysql_query("SELECT date_time, from_user_name, message 
                         FROM message_log
                         WHERE date_time BETWEEN '$log_ini' AND '$log_fim' 
                         AND (from_user_name = '$usuario' OR from_user_name = '$atendente')")or die(mysql_error());

It simple returns null. I'm sure there are records that match the select. I have checked also the date_default_timezone_set and set it to same time to the server. I know this is a silly one, but I'm getting really frustrated! It's been hours I'm trying everything, FROM_UNIXTIME function, no coman, coman, <> operators, strotime in the variables before, and nothing works... Can someone point out where is the mistake? Thanks a lot!


I got the problem, it's the date_time fiels, which is actually not really unixtimestamp, but unix time + 3 digits. SORRY, was my mistake and the answer was good. I'm open another question to find now how to deal with this fiel in this formt. Thanks a lot!

share|improve this question
Have you tried outputting the query to see what values $log_ini and $log_fim contain? –  Pekka 웃 Feb 14 '12 at 17:42
Try running the query in a mysql terminal. Then run "show warnings" –  thetaiko Feb 14 '12 at 17:46
where do the variables you're injecting into the query come from? If they're user input and not validated or escaped then you're risking an SQL injection attack. You might want to look into prepared statements (which also means switching to mysqli or PDO) –  GordonM Feb 14 '12 at 17:47
@Pekka yes, in this case $log_ini 2012-02-14 08:38:20 and $log_fim 2012-02-14 08:41:25 ;S –  Adry Feb 14 '12 at 17:48
@GordonM no, they are simple coming from another select, from other database and they are formated 00-00-00 00:00:00 –  Adry Feb 14 '12 at 17:50

1 Answer 1

up vote 0 down vote accepted

Ok, your date_time field is unix_timestamp? That's just an integer, and you're forcing MySQL to do a 'between' operation on that - MySQL has no native "unix timestamp" format, it's just a convenience function to convert integer<->datetime

For the where clause to work, you'd need

WHERE FROM_UNIXTIME(date_time) BETWEEN '...' AND '...'
... or ...

so you're properly comparing apples to apples, and not the apples to oranges you are.

share|improve this answer
hi Marc B I tryied again this 2 suggestions, I've been trying them also this afternoon but the result is always null... >;( One thing I notice is that if I take out the single coman ' like in your exemple I will run in a sql error –  Adry Feb 14 '12 at 18:01
yes, '2012-02-14 00:00:00' without the quotes is a math operation and some colon-separated zeroes, which isn't valid SQL syntax. –  Marc B Feb 14 '12 at 18:03
ok, just a comment, trying to figure out! :) –  Adry Feb 14 '12 at 18:04
also, make sure that your dates are in the proper order. it should be 'lower' AND 'upper'. If you do 'upper' AND 'lower' it won't work. the between syntax basically translates to where (x >= 'lower') AND (x <= 'upper'). Reverse the order makes it impossible to fulfill the restriction. –  Marc B Feb 14 '12 at 18:06
anyways, the comparison works althought the date format, if I'll do a select where date_time > $log_ini I'll get all the results up to this. But, if I do select where date_time > $log_ini and date_time < $log_fim I'll get null again. –  Adry Feb 14 '12 at 18:06

Your Answer


