Jump to content

Archived

This topic is now archived and is closed to further replies.

Bhaal

'DatePosted' field defined as int(10)

Recommended Posts

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...

Share this post


Link to post
Share on other sites
[!--quoteo(post=357935:date=Mar 24 2006, 02:33 PM:name=Bhaal)--][div class=\'quotetop\']QUOTE(Bhaal @ Mar 24 2006, 02:33 PM) [snapback]357935[/snapback][/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 ;)

Share this post


Link to post
Share on other sites
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:

[code]
$q1 = " select * from listings, members where members.MemberID = listings.MemberID ";
[/code]

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:

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

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')?



Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.)

Share this post


Link to post
Share on other sites

×

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.