BETWEEN dates unix timestamp issue.

[iPixel]

Long story short, i have a table where the entry_date is stored as an int(10) unix timestamp. No i cannot alter the table so i must use it how i have it.

 

if i do a.entry_data >= 'unixtimestamp' this will work fine. But for some reason neither of the following work

 

a.entry_date BETWEEN 'todays unixtimestamp' AND 'nextweeks unixtimestamp'

 

or

 

a.entry_date >= 'todays unixtimestamp' AND a.entry_date <= 'nextweeks unixtimestamp'

 

Any ideas why this returns zero results?

 

Here's the full query...

 

SELECT 
a.status AS STATUS, 
a.title AS EventTitle, 
a.url_title AS URLtitle, 
a.entry_date AS EventDate,
b.field_id_228 AS Image, 
b.field_id_8 AS TIME, 
b.field_id_9 AS Description
FROM  
ee_table1 a, ee_table2 b
WHERE 
a.weblog_id = '12' AND
a.entry_id = b.entry_id AND
(a.status = 'Open' OR a.status = 'Featured') AND
a.entry_date BETWEEN '1317400474' AND '1317389674'
ORDER BY a.entry_date ASC;

[Pikachu2000]

You have the arguments for BETWEEN in the wrong order. The lower value needs to be first.

[iPixel]

No it's not actually, i have 2 php scripts that print out the unixtimestamp. The lower timestamp is on the left.

 

Here's the script i use to get unixtimestamps.

 

<?php

// Note: Unix timestamp maker
$unixtimestamp = mktime(date("h i s n j y", strtotime("today")));
echo "Unix Timestamp : " . $unixtimestamp . "<br /><br />";


$unixtimestamp2 = mktime(date("h i s n j y", strtotime("+1 week")));
echo "Unix Timestamp (nxt week): " . $unixtimestamp2 . "<br /><br />";


echo date("h i s n j y", strtotime("+1 week"));

?>

 

[Pikachu2000]

That isn't reflected in the query string you posted

:

WHERE

a.weblog_id = '12' AND

a.entry_id = b.entry_id AND

(a.status = 'Open' OR a.status = 'Featured') AND

a.entry_date BETWEEN '1317400474' AND '1317389674'

[iPixel]

what do you mean it's not reflected? the timestamp will be different every time you run that php script because of seconds. Either way i flipped them just to try and nothing still.

[PFMaBiSmAd]

Here's the script i use to get unixtimestamps.

 

If you look at those two numbers, you will see that the next week one is less-than the today one.

 

mktime() does not take the string output of a data() function as a parameter. It takes up to six comma separated parameters. Your code is supplying the output from the date() function as the first (hour) parameter to the mktime() function.

 

All you need to use are the two strtotime() statements (to get the correct result) -

$unixtimestamp = strtotime("today");
echo "Unix Timestamp : " . $unixtimestamp . "<br /><br />";


$unixtimestamp2 = strtotime("+1 week");
echo "Unix Timestamp (nxt week): " . $unixtimestamp2 . "<br /><br />";

[iPixel]

Yep you were right, my unixtimestamp script was bad.

 

THANK YOU!