May 20, 2014 at 8:06 am
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.
May 20, 2014 at 8:40 am
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.
May 20, 2014 at 8:43 am
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
May 20, 2014 at 8:43 am
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)
May 20, 2014 at 9:13 am
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. 😎
May 20, 2014 at 12:53 pm
May 21, 2014 at 12:48 am
I presume you mean this one?
http://qa.sqlservercentral.com/articles/SQL+elapsed+times/101971/
I shall read and digest it!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply