timestamp???

[php4_23]

I want to be able to select rows from a table where they are not older then 30 days.
How do I compare it to the current date??
These timestamps are kinda consfusing.
thanks

[JDcrack]

I am assuming your table is setup like this:

name    age     date

| Jack   | 23   | 2003-03-04 |
|Sarah  | 23   | 2003-03-27 |

| Jen    | 12    |2002-12-02 |

So you would like a function that would only return Jack and Sarah.
correct?

To do any sort of mathematical comparison to dates, it is suggested to use the mktime() function
From the manual:
mktime() is useful for doing date arithmetic and validation, as it will automatically calculate the correct value for out-of-range input. For example, each of the following lines produces the string "Jan-01-1998". Example 1. mktime() example

echo date ("M-d-Y", mktime (0,0,0,12,32,1997));
echo date ("M-d-Y", mktime (0,0,0,13,1,1997));
echo date ("M-d-Y", mktime (0,0,0,1,1,1998));
echo date ("M-d-Y", mktime (0,0,0,1,1,98));

You can also do it directly from your SQL statement:
http://www.mysql.com/doc/en/Date_and_time_functions.html

//The following query selects all records with a
// date_col value from within the last 30 days:
SELECT something FROM tbl_name
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

[php4_23]

Okay, I now got it figured out.......I think😃

thanks

[php4_23]

Ok , no i dont.
I tried that but it does not work.

My date is stored in a bigint field in the form of a unix timestamp.

I want it to be something like this:

SELECT * FROM messages WHERE blah = blah AND TO_DAYS(NOW()) - TO_DAYS(message_date) <= 30;

unfortunatly this does not seem to work.

[JDcrack]

unfortunatly this does not seem to work.

Please try to be more specific.
Are you getting any specific errors?
Is it not returning any results?

There is no reason this function should not work.
You may have some sort of syntax error, or perhaps nothing in your DB meets the requirements of blah=blah and 30 days old.
Also: in my date column my timestamp is in the unix form of YYYYMMDD
ex. 20020916
Try running this as a test script and see what you get
(notice that I left out blah=blah)

$result = mysql_query("SELECT * FROM messages WHERE TO_DAYS(NOW()) - TO_DAYS(date) <= 30")
  	    or die('Query failed: ' . mysql_error());
//replace (date) with the name of your date column
$i = 1;
while ($myrow=mysql_fetch_array($result)){
echo "$i.  ". $myrow["id"]."<br>";
$i++;
}

[Pig]

A timestamp is the number of seconds since the unix epoch, NOT yyyymmdd. You can do simple conversion with strtotime.

In your where statement, make the value it is comparing be:
strtotime("now - 30 days");

[Pig]

$result = mysql_query("SELECT * FROM table_name WHERE table_date_field > " . strtotime("now - 30 days") );