1

So I am working on a database for airports and i want to find the duration of flight. There is a table named flight that has id... dep_time(departure time), arr_time(arrival time) declared as time without time zone.

The problem is that one of the flights departs at 23:00:00 and arrives at 02:00:00 of the next day.

So in general i was using arr_time - dep_time but that gives me a negative result for the specific flight (-21:00:00).

So what i want to get to is that by chance I used - dep_time + arr_time and got the right result (03:00:00)... Can someone explain? I am so confused

(I dont want a solution cause I got it, I would like an explanation. Also I have to use time and not timevariable as it is specified in the project)

EDIT#1: Guys I dont want a solution for the problem, I have solved it. I want to know why there is a difference in the result while there shouldnt be.

PROvlima
  • 541
  • 1
  • 4
  • 9
  • 2
    you can't use just time values, since your times stretch across multiple days. You'd need to do `datetime1 - datetime2`, basically. e.g. consider a 24 hour flight, departing 01:30 and arriving 01:30 - total time flown: 0 hours. but if you have `departs Monday 01:30, arrives Tuesday 01:30`, then you'd get `1 day`. – Marc B May 12 '15 at 17:03
  • As I said, the tables must contain only dep and arr time (this is an instruction I have to follow). And AGAIN I am not trying to solve a problem, i want an explanation: why there is a difference in the result while there shouldnt be – PROvlima May 13 '15 at 02:14

1 Answers1

0

The baseline for type time is one day. Internally - it is number of second from 0:0:0. It works simply when both values are from one day. But when these values are from different days, you can get negative value due overflow over maximum for this type (24 hours - it is difference between these two baselines). This error can be fixed by addition 24 hours:

db2=# select '02:00:00'::time - '23:00:00';
┌───────────┐
│ ?column?  │
╞═══════════╡
│ -21:00:00 │
└───────────┘
(1 row)

db2=# select '02:00:00'::time - '23:00:00' + '24hours';
┌──────────┐
│ ?column? │
╞══════════╡
│ 03:00:00 │
└──────────┘
(1 row)

Same situation can be with dates in week, months. You can calculate in higher order (or in absolute values) or you have to fix distance in baselines.

So tue - mon = 1 .. (2 - 1), but Sun - Sat = -6 .. (0 - 6) these days are in different weeks, so you have to fix it -6 + 7 = 1

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94