calculating elapsed time between datetimes for travel

Question

calculating elapsed time between datetimes for travel

Chris Quinn-821458

Hall of Fame

Points: 3192
More actions
May 20, 2014 at 8:06 am

#396784

Very interesting one here - I have a requirement to be able to calculate the transit time between international locations when I have the flight departure time and flight arrival times and departure and arrival locations.
We have a problem though, in that the datetime stored are local times, and the journeys can involve crossing the international date line in either direction, so it is possible for the arrival time to be earlier than the departure time!
To give a famous example, if you flew Concorde from London to Washington, you could depart at 10am on the 1st of June (London time) and arrive at 8.00am on 1st June (Washington time)
Even worse, you could leave Brisbane at 2.00 am on the 1st June, cross the International Date Line and arrive at San Francisco at 23:00 on 31st May!
Can anyone suggest a way I could calculate the elapsed times? I presume I would need a lookup of city and time zones, but am sure I would need to take account of daylight saving time changes etc.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 1

Any chance you can use the datetimeoffset datatype? This will save the timezone information along with the local time. Datetimeoffset is a new(ish) feature in SQL 2012. I admit, though, I have not played around with it enough to know if it will address all of your problems.

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 2

The dates and times we get are provided by an external source and do not contain the offsets, so we would need to derive these from location, from a lookup of location and time zone information

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 3

Well, that was a quick test, anyway:

declare @boston datetimeoffset = '2014-05-20 12:00 -05:00'

declare @chicago datetimeoffset = '2014-05-20 12:00 -06:00'

select datediff (hh, @boston, @chicago)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

In general, converting the local times to UTC times is the way to solve the problem.

The real problem is converting the local time to UTC time. It will probably require a lookup from a table to get the time zone offset from UTC for each location, and a lookup of some time zone table to find if daylight savings line is in effect at a location and local time and what the DST offset is. Do not assume that the offsets for time zones are whole hours; the offset for India is UTC +5:30, so it's best to store the offset as minutes for both the time zones and daylight savings time.

For something that operates on a 24 hour schedule, you can also run into problems with the time being ambiguous for times that fall within the fallback time range. For example, for US Eastern time, on Sunday, November 2, 2014, 02:00:00 clocks are turned backward 1 hour to Sunday, November 2, 2014, 01:00:00 local standard time. If you get a time of 2014-11-01 01:30, is it before the time change or after the time change?

It would be ideal if you could get the data from your source in UTC time, so good luck with that. 😎

mickyT SSChampion Points: 10360 More actions · Answer 5

Hi

Dwain did an article on Departures and Arrivals[/url], which if I remember correctly discussed the issues of calculating elapsed time between time zones, including daylight savings time, date lines etc.

Micky

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 6

I presume you mean this one?

http://www.sqlservercentral.com/articles/SQL+elapsed+times/101971/

I shall read and digest it!