Checking dates in a database against UNIX time

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.

Can you provide a schema for your table? Are those text columns? — mkasberg, Nov 05 '15 at 21:01
Please provide a [fiddle](https://www.sqlfiddle.com), with a sample of your data. — hd1, Nov 05 '15 at 21:02

score 0 · Accepted Answer · edited May 23 '17 at 10:27

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.

Checking dates in a database against UNIX time

1 Answers1