Jump to content

'DatePosted' field defined as int(10)


Bhaal

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...
Link to comment
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 ;)
Link to comment
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')?



Link to comment
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!

Link to comment
Share on other sites

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.