0

I have some trouble comparing the fields to current time.

I have two fields in MySQL, who record the start time and end time of a lecture.

What I'm trying to do is check if the current time is during the start time and the end time.

$starttime and $endtime are in forms of DateTime.

The code's here:

$now = time();
$compare = mysql_query("SELECT * FROM lecture WHERE strtotime(starttime) < '$now' AND '$now' < strtotime(endtime)");

But it doesn't work.

I also tried this:

$now = new DateTime;
$compare = mysql_query("SELECT * FROM lecture WHERE starttime < '$now' AND '$now' < endtime");

But it broke too.

Could anyone tell me how to realize it? Many thanks.

John Woo
  • 258,903
  • 69
  • 498
  • 492
Matt
  • 565
  • 1
  • 7
  • 12
  • what is the datatype of column `starttime`? can you give sample records? In mysql, you can use `STR_TO_DATE` to convert it to date (*with time*) – John Woo Dec 29 '12 at 10:27
  • 2
    [Please, don't use `mysql_*` functions in new code](http://stackoverflow.com/q/12859942). They are no longer maintained and the deprecation process has begun, see the [red box](http://php.net/mysql-connect). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli); [this article](http://php.net/mysqlinfo.api.choosing) will help you decide which. If you choose PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). – vascowhite Dec 29 '12 at 10:27
  • @JW.: its format is DateTime, like this: 2012-12-29 08:32:00 – Matt Dec 29 '12 at 10:32
  • @vascowhite: Thank for the link and the details! – Matt Dec 29 '12 at 10:33
  • @JW.: I've tried STR_TO_DATE(starttime) < '$now', but it doesn't work.. – Matt Dec 29 '12 at 10:37
  • you don't need to use it since you have mentioned that the records are fomatted like this `2012-12-29 08:32:00`. by the way `STR_TO_DATE` accepts two params: columnName, and the format. – John Woo Dec 29 '12 at 10:38

2 Answers2

7

use BETWEEN

SELECT * 
FROM   lecture 
WHERE  NOW() BETWEEN starttime AND endtime
John Woo
  • 258,903
  • 69
  • 498
  • 492
1

$compare = mysql_query("SELECT * FROM lecture WHERE starttime < mysql_function AND mysql_function < endtime");

Replace mysql_function with one of the following function

1) SYSDATE(): example output as 2012-12-29 16:03:20

2) CURDATE(): example output as 2012-12-29

3) UNIX_TIMESTAMP(): example output as 1356777278

Depending upon 'startime and endtime' format replace one of the above function in the query.

Mahesh.D
  • 1,691
  • 2
  • 23
  • 49