Getting just Seconds out of a unix timestamp

[brentman]

I have users who signed up for an email list and there is a table of them and the join date is in unix timestamp. I do not care about the date, only the time they signed up. I am putting together code to be run on cron to decide who gets emailed. It will be run probably every hour. When it runs the first thing I need it to do is select all users who from the current time through the next hour registered. This way I can then email people around the time they initially joined my site.

 

Here is what I have:

 

//$input is the minutes range (which will be most likely an hour so this variable should be 60)

function to_email($input) {
	require_once('dbconnect.php');
	$cur_hours = date('H');
	$cur_minutes = date('i');
	$cur_seconds = ($cur_minutes + ($cur_hours * 60)) * 60;
	
	$end_seconds = $input * 60;
	$end_seconds = $cur_seconds + $end_seconds;
	
	$result = mysql_query("SELECT * FROM usertable WHERE " . $cur_seconds . " <= (HOUR(opt_time)*60*60) + (MINUTE(opt_time)*60) + SECOND(opt_time) AND
'" . $end_seconds . " > (HOUR(opt_time)*60*60) + (MINUTE(opt_time)*60) + SECOND(opt_time)'");
	$row = mysql_fetch_array($result);
}

The php part works as what I think is needed. But the query does not. I am not very good at MySQL queries so don't judge.

 

[Barand]

Seems easier to select those where the current hour matches the hour they signed up

SELECT ... WHERE HOUR(NOW()) = HOUR(FROM_UNIXTIME(opt_time))

[brentman]

Well that looks pretty easy, I like it. But what happens if I need only 30 minutes? What about 2 hours? I am not quite sure of how often the cron job will run yet.

[brentman]

Also btw that query didn't work. 

[Barand]

Well no, you have to replace the "..."

[brentman]

function to_email($input) {
	require_once('dbconnect.php');

	echo time();
	
	$result = mysql_query("SELECT * FROM usertable WHERE HOUR(NOW()) = HOUR(FROM_UNIXTIME(opt_time))");

	$row = mysql_fetch_array($result);
	echo mysql_num_rows($row);
}

usertable

one records opt_time value is 1424557192

 

I ran the script and it echoed: 1424557019

 

And this error:

Warning: mysql_num_rows() expects parameter 1 to be resource, array given in /home/mysite/public_html/api/index.php on line 45

Edited February 21, 2015 by brentman

[Barand]

It was working when it left the shop EG

mysql> SELECT * FROM test_time;
+------------+
| time       |
+------------+
| 1424476800 |
| 1424480400 |
| 1424484000 |
| 1424487600 |
| 1424491200 |
| 1424494800 |
| 1424498400 |
| 1424502000 |
| 1424505600 |
| 1424509200 |
| 1424512800 |
| 1424516400 |
| 1424520000 |
| 1424523600 |
| 1424527200 |
| 1424530800 |
| 1424534400 |
| 1424538000 |
| 1424541600 |
| 1424545200 |
| 1424548800 |
| 1424552400 |
| 1424556000 |
| 1424559600 |
+------------+

mysql> SELECT
    ->     time
    ->     , FROM_UNIXTIME(time)
    -> FROM test_time
    -> WHERE HOUR(NOW()) = HOUR(FROM_UNIXTIME(time));
+------------+---------------------+
| time       | FROM_UNIXTIME(time) |
+------------+---------------------+
| 1424556000 | 2015-02-21 22:00:00 |
+------------+---------------------+

[Barand]

in your code

echo mysql_num_rows($row);

should be

echo mysql_num_rows($result);

Edited February 21, 2015 by Barand

[brentman]

Just did that right when I saw that ugh *facepalm*.

 

Hourly is close enough for now.

 

Thanks.