Jump to content


Photo

'DatePosted' field defined as int(10)


  • Please log in to reply
6 replies to this topic

#1 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 24 March 2006 - 02:33 PM

Will this work?

I'd like a page that displays 'newest' members. This has to be pretty common, right?

There's a DatePosted field defined as int(10) that holds a numeric string - I'm assuming this is a Unix time stamp. (Sample data: 1141830780)

This DatePosted field records the date of registration.

I need to display members who have registered 45 days or less. Once they pass 45 days as a registered user, they'd just drop out of the recordset from the query.

So - will this work even though DatePosted is not defined as a DATE but as int?:

mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 45 DAY) <= DatePosted;

Thanks for any assistance...

#2 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 24 March 2006 - 05:19 PM

[!--quoteo(post=357935:date=Mar 24 2006, 02:33 PM:name=Bhaal)--][div class=\'quotetop\']QUOTE(Bhaal @ Mar 24 2006, 02:33 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Will this work?

I'd like a page that displays 'newest' members. This has to be pretty common, right?

There's a DatePosted field defined as int(10) that holds a numeric string - I'm assuming this is a Unix time stamp. (Sample data: 1141830780)

This DatePosted field records the date of registration.

I need to display members who have registered 45 days or less. Once they pass 45 days as a registered user, they'd just drop out of the recordset from the query.

So - will this work even though DatePosted is not defined as a DATE but as int?:

mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 45 DAY) <= DatePosted;

Thanks for any assistance...
[/quote]

So you know the syntax, but you haven't tried? Silly man.

I doubt that it will work, though.. Option 1 would be to preserve the data (like back the table up into a temp table) and then alter the table to date format, or Option 2 would be to write your own Unix Timestamp date function for the current day, and then one for 45 days ago, and construct a WHERE statement.

Either approach would be quite simple, but let me know if you need help ;)

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#3 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 25 March 2006 - 05:55 PM

Yep I need help...I feel like I'm lost on Query Island. I've tinkered quite a bit, but the raft keeps sinking. (And then Walt gets taken..."right from my own HANDS!!!!")

The original query is simple enough:

$q1 = " select * from listings, members where members.MemberID = listings.MemberID ";

Then, there's a field that I need to filter the results down from called [DatePosted] - this field is defined as int(10).

I do know that the following snippet 'converts' [DatePosted] into an actual date:

$PostedDate = date('d-M-Y', $a1[DatePosted]);

How would I take the results from $q1 and re-query them based on a 45-day range (from today) of '[DatePosted]' (or rather, '$PostedDate')?





#4 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 26 March 2006 - 10:26 PM

My bump, my bump, my bump...my little query bump...check it out...

Maybe I need to restate the problem, since things can get confused:

I need to find records based on a date; basically, find all records within a 45-day range from 'today'.

A twist is that the 'date' field isn't a date datatype, it's int(10).

(This is inherited code with an existing data structure...as an aside, would changing the datatype from int to date screw up the code that writes to this field?)

Help appreciated!



#5 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 28 March 2006 - 11:17 AM

One more bump...any help appreciated!

#6 Eddyon

Eddyon
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationEngland

Posted 28 March 2006 - 12:15 PM

set your time limit as 45 days:

$limit_time = time() - 3888000;

and then in your query include this where clause:

WHERE UNIX_TIMESTAMP(dateposted) >= $limit_time

Try giving that a go, let us know how you get on.

#7 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 29 March 2006 - 05:29 AM

Thanks for the reply, Eddyon...but the results of that where clause were blank. (No record in the test data is over 2 weeks old, so it should catch all of them, but it seems to have filtered out all of them.)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users