Comparison between two timestamps in query

Question

I want to run a cronjob which check in db table where timespamp is less than current time + 1 month. But not sure how can i do that.

I have tried something but that not works.

$buy_time=strtotime($row['sdate']);
$current_time= strtotime("now");
$diff=$current_time - $buy_time; 

$SQL = "UPDATE product SET status=0 WHERE sdate <'$diff'";
$res= mysqli_query($link,$SQL);

This question has some answers that look as though they'd work for you: http://stackoverflow.com/a/3923228/1352271 — JohnnyFaldo, Aug 02 '13 at 13:56

score 1 · Answer 1 · answered Aug 02 '13 at 13:57

1

Assuming you're using native mysql date/datetime fields for that sdate field, then don't use PHP to generate dates and paste them into the query, do it all in MySQL directly:

... WHERE sdate >= (NOW() - INTERVAL 1 MONTH)

answered Aug 02 '13 at 13:57

Marc B

356,200
43
426
500

and use `DATE(FROM_UNIXTIME(sdate))` if `sdate` is a unix timestamp – NDM Aug 02 '13 at 14:05

score 0 · Answer 2 · answered Aug 02 '13 at 14:03

0

$currentTime = time();
$timePlusMonth = $currentTime+30*24*60*60; //30 days * 24 hours * 60 minutes * 60 seconds
$timePlusMonth = date("Y-m-d H:i:s", $timePlusMonth);

$query = "UPDATE product SET status=0 WHERE sdate < '$timePlusMonth'";
mysqli_query($link, $query);

your 'sdate' column must be in Y-m-d H:i:s format (2013-08-02 16:02:45)

answered Aug 02 '13 at 14:03

GTCrais

2,039
2
26
32

i want to update to update status where `sdate-currenttime > 1 month` – Aryan Sareen Aug 02 '13 at 14:09
This will faily badly if you run it on March 1st. You'll end up back in January and end up pulling up two months. – Marc B Aug 02 '13 at 14:10
thn what to do...i am doing this for cancelling the product on xpiration – Aryan Sareen Aug 02 '13 at 14:13

Comparison between two timestamps in query

2 Answers2