根据UNIX时间检查数据库中的日期

Question

I have a table showing flights. The dataset is automatically imported so I would prefer not altering data in the database.

The set has a start and end date for a flight. The columns are called "start" and "end", and contains for example the start date "4-Nov-15" and end date "17-Dec-15".

How can I build a query to check if a row is within these two timeframes?

$currenttime = time();
mysql_select_db("wifva");
$result = mysql_query("SELECT * FROM flights WHERE start <= '$currenttime' && end >= '$currenttime' ORDER BY deptime");
while($row = mysql_fetch_array($result))
{
?>
<tr class="hover" style="text-align: left;">
<td class="border_bottom"><?php echo $row['deptime'];?>z</td>
<td class="border_bottom"><?php echo $row['callsign'];?></td>

The above code does show data in my table, but doesn't output the correct information (assuming that php/sql doesn't automatically recognize date format etc).

Any help is appreciated.

Answer 1

I believe you can use "UNIX_TIMESTAMP" in your query to do that. It's use is described well here:

SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))

OR

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))

I found this in this SO answer by user query_master (https://stackoverflow.com/users/1352125/query-master): MySQL convert datetime to Unix timestamp

You should be able to adapt it to fit your needs, though I haven't tested it with your particular date format that's currently in the DB.