How to subtract two sql datetime fields

Question

I have two datetime values on sql database and I want to subtract them and get the difference as a number of hours.

So far I have created three values. Deptime and ArrTime as datetime values and here are my questions.

1) What value shall the third table be? can it be number because I want to use it for statistics?

2) How can I subtract the Deptime and Arrtime and store the differnce in the FlightTime?

Thanks in advance

possible duplicate of [Calculate difference between two datetimes in MySQL](http://stackoverflow.com/questions/10907750/calculate-difference-between-two-datetimes-in-mysql) — 2kai, Apr 23 '15 at 14:05

score 9 · Answer 1 · answered Apr 23 '15 at 14:04

9

DATEDIFF can get you your hours.

SELECT DATEDIFF(HOUR, Deptime,Arrtime ) AS FlightTime FROM YourTable.

answered Apr 23 '15 at 14:04

Jeffrey Eldredge

899
7
12

Most probably a very noob question but I will write this code to the SQL code inside my phpmyadmin or on the code I write for the web application? – Aldaron47 Apr 23 '15 at 14:27
Likely in your web app. Especially if you plan on doing it multiple times, on computing these hours on the fly. (Heh. Fly.) – Jeffrey Eldredge Apr 23 '15 at 15:07
As I understand it's MySQL implementation of SQL. So `DATEDIFF` would return difference in days. – 2kai Apr 24 '15 at 06:25

score 1 · Answer 2 · answered Apr 23 '15 at 14:05

If you are using tSQL then you could use the DATEDIFF function. You can use this function with hours but if you want fractions of hours then you can use minutes and then convert back to hours.

fraction of hours

SELECT CAST(DATEDIFF(MINUTE, Deptime,Arrtime) AS DECIMAL(30,10))/60 AS FlightTime
FROM TableName

whole hours

SELECT CAST(DATEDIFF(HOUR, Deptime,Arrtime)) AS FlightTime
FROM TableName

Since it is a calculated field, I would just create a view with the DATEDIFF function for the calculated field.

score 0 · Answer 3 · edited May 23 '17 at 12:14

PHP has its own DateInterval routines that might give you more flexibility (and portability) than doing it in SQL.

$datetime1 = new DateTime($DepDate);
$datetime2 = new DateTime($ArrDate);
$FlightTime = $interval->format('%h');

I'm looking at your progress and see a couple of red flags. Since you're accepting user input and inserting it into a database, you should at the very least be using prepared, parameterized insert statements.

$PiRepSubmissionSqlQuery = '
  INSERT INTO Pireps (
    PID, FLID, DEPID, ARRID, 
    FlightPlan, AircraftType, Fuel, 
    DepDate, ArrDate, FlightTime, 
    Remarks, Server
  ) 
  VALUES (
    ?, ?, ?, ?,
    ?, ?, ?,
    ?, ?, ?,
    ?, ? 
  )
';

// This is just an example - don't forget to check for errors
$sth = mysqli_prepare($mydb, $PiRepSubmissionSqlQuery);
mysqli_stmt_bind_param(
  $sth, 
  'iiiisssssiss', // these should correspond to your SQL datatypes
  $PID, $FLID, $DEPID, $ARRID,
  $FlightPlan, $AircraftType, $Fuel,
  $DepDate, $ArrDate, $FlightTime,
  $Remarks, $Server
);
mysqli_stmt_execute($sth);

score 0 · Answer 4 · answered Apr 24 '15 at 06:15

You should use mysql function TIMESTAMPDIFF to get difference between dates. So your query should look like this:

$PiRepSubmissionSqlQuery = "INSERT INTO Pireps (PID, FLID, DEPID, ARRID, FlightPlan, AircraftType ,Fuel ,DepDate ,ArrDate, FlightTime  ,Remarks ,Server) 
                       VALUES ('$PID', '$FLID', '$DEPID', '$ARRID','$FlightPlan' ,'$AircraftType' ,'$Fuel','$DepDate' ,'$ArrDate' ,TIMESTAMPDIFF(HOUR, ArrDate, DepDate), '$Remarks' ,'$Server')";

How to subtract two sql datetime fields

4 Answers4