TimeStamp

Anon

Dear All experts,

Hi, me again!

This time I have a Time comparison problem would like to ask all you advice.

In my MySQL table (e.g. product), the date of the data is one of the field of the table "product". It is stored in timestamp, i.e. the "date" of data A will be 2002-04-02 13:23:04.

Original query:
$result = = mysql_query("SELECT * From product");

Now, I would like to query my mysql to show all data whose time difference comparing with this moment is only less than 7 days.

I have a variable $current = date("Y-m-d H:i:s");

I hope my new query as something like that

$result = mysql_query("SELECT * From product WHERE ($current - date) <= 7 days");

Any comment and solution?

stezz

you'll need to take a seperate timestamp in your script. Then the timestamp you retrieve from your query you will be able to compare with the timestamp you just took in your script. You can work out which ones are less then 7 days like that.

e.g.

<?php
$time = time() - (7 days in seconds); // timestamp. This will give you the timestamp 7days ago.

$result = mysql_query("SELECT * FROM Product WHERE timestampintable <= '$time';");

Something along those lines. Hope it gets you on the right track.

Regards, Stezz.

Anon

It cannot work, as the "timestampintable" is stored in format "2002-03-14 22:10:45"

But the $time is in "1017888359" format

stezz

hey.. you've probably resolved the problem by now, but just in case, here's my solution..

The TimeStamp in your table should be whatever the time() functions returns.. something like 43533543, whatever..

So, start by inserting the un-formatted time into your table then this will work..

$time = time() - (7 days in seconds); // this gives u the time exactly 7 days ago.

then you can query your database by doing..

$result = mysql_query("SELECT * FROM table where timeintable <= '$time';");

Regards, Stezz.

Weedpacket

MySQL Manual
Here is an example that uses date functions. The following query selects all records with a date_col value from within the last 30 days:
mysql> SELECT something FROM tbl_name
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

Ah, the power that can be got from thirty seconds with the manual.

planetsim

Yup. But when your a Noob its hard to comprehend the Manual. making learning in any language hard.

Weedpacket

Those are almost the very first lines in the MySQL manual under "Date and Time Functions". It only took me a full thirty seconds because I had to unpack the archive.

As for comprehension, compare the phrase

"within the last 30 days"

with

"time difference comparing with this moment is only less than 7 days"