Jump to content

Compare a Range of Date's w/Unix Timestamp....


Recommended Posts

heres a small section of my code...

 

	$query = "SELECT userid, username, postid, dateline, COUNT(*) AS `postoccurs` FROM forum_post WHERE dateline BETWEEN 1193875200 AND {another dateline} GROUP BY userid ORDER BY userid";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
	$userid = $row['userid'];
	$username = $row['username'];
	$postoccurs = $row['postoccurs'];
	$postid = $row['postid'];
	$dateline = $row['dateline'];
}

 

Anyways, Im trying to use this query & obviously the problem I'm having is, I'm comparing the datelines which isn't doing me much good...

 

Is there a better way to compare the unix timestamps... i dunno, i've been working on this all afternoon & my brain is fried! :/

Link to comment
https://forums.phpfreaks.com/topic/75707-compare-a-range-of-dates-wunix-timestamp/
Share on other sites

I have no idea, I've never had a problem using BETWEEN before...

 

IE... I look for results between 2007-05-22 & 2007-09-02...

 

It will return results between the 2 dates but also before 2007-05-22 & after 2007-09-02

That seems impossible... are you comparing dates & timestamps? Post some sample data.

That seems impossible... are you comparing dates & timestamps? Post some sample data.

 

Ok so lets say i do between: 2007-10-31 & 2007-11-30

 

Well, i get data like this:

1192229403 & 2007-10-12

1192316034 & 2007-10-13

1192590308 & 2007-10-16

1192198106 & 2007-10-12

1192051263 & 2007-10-10

1193083001 & 2007-10-22

1192426313 & 2007-10-15

1194017537 & 2007-11-02

1192224043 & 2007-10-12

1192254836 & 2007-10-13

1193507903 & 2007-10-27

1193764122 & 2007-10-30

1192300742 & 2007-10-13

1192822359 & 2007-10-19

1192012799 & 2007-10-10

1193090810 & 2007-10-22

1193876512 & 2007-10-31

1192335514 & 2007-10-13

1192150990 & 2007-10-11

For what query? That's a mix.

 

SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d') AS 'date_formatted', userid, username, postid, dateline, COUNT(*) AS `postoccurs` FROM forum_post WHERE 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' GROUP BY userid ORDER BY userid

I was trying to make it all easier to read by removing this part of the query when showing you:

WHERE 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' AND forumid=385 OR forumid=386 OR forumid=387 OR forumid=388 OR forumid=389 OR forumid=392 OR forumid=395 OR forumid=396

 

When i pull that out (The: " AND forumid=385 OR forumid=386 OR forumid=387 OR forumid=388 OR forumid=389 OR forumid=392 OR forumid=395 OR forumid=396", it works...

 

 

You're missing parens, it's a precendence thing:

 

WHERE ( 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' ) AND ( forumid=385 OR forumid=386 OR forumid=387 OR forumid=388 OR forumid=389 OR forumid=392 OR forumid=395 OR forumid=396 ) 

 

but mysql won't get it wrong if  you use IN:

 

WHERE 'date_formatted' BETWEEN '2007-10-31' AND '2007-11-30' AND forumid IN (385, 386, 387, 388, 389, 392, 395, 396 )

 

but you should probably have the parents anyway.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.