calculating elapsed time between datetimes for travel

Question

calculating elapsed time between datetimes for travel

Viewing 6 posts - 1 through 7 (of 7 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!