Time Difference

[Steve]

I am trying to calculate elapsed travel times for flights. My plan is
to enter the local departure time, the departure city and the local
arrival time and city. These times would be standardised to GMT and a
date difference calculated which would be the theoretical elapsed time
of the flight

I have one Table called Cities which has the name of the city and the
difference from GMT eg Sydney is 10 (ie 10 hrs ahead of GMT), LA is -7
ie 7 hours behind GMT.

I then have a second table called flights with data to record the
departure and arrival times (local ) of planned flights, using combo
boxes to choose the arrival and departure cities. I want the data to
calculate the elapsed time. I assume I will need a flight header
table and a flight detailed table to allow me to calculate when more
than one flight is involved but have never used time differential
calculations before so am looking for some basic starting points

Appreciate any help
Steve

[Allen Browne]

There are a few issues here, Steve - such as what to store verses what to
display - and the best solution will depend on what you are trying to do
with your data.

An argument could be made for storing everything on the GMT clock, and then
displaying the date/time for the local timezone where appropriate. That
would really simplify any calculations you need to make, e.g. when a pilot
needs a break, when a maintenance needs to be done, which planes are in the
air at the same time, hours travelled, and how long flight times are.

One of the issues is knowing the history of times at the various locations
you must track. Daylight saving adjustments are not consistent throughout
the years (e.g. Sydney was different the year it held the Olympics.) You
therefore need either a table to record all time changes for all locations,
or some way to query that from the OS. (I think I read that Vista has that
history, but XP does not.)

Anyway, if you store everything on GMT, and you know when all the timezones
change, you can interface it to display local time. You can even use some
unbound controls overlaying the calculated local time to allow the user to
enter local times, and convert them to GMT times for storage. Post back if
you need more help on that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve" <st**********@gmail.comwrote in message
news:8c**********************************@f24g2000 prh.googlegroups.com...

>I am trying to calculate elapsed travel times for flights. My plan is
to enter the local departure time, the departure city and the local
arrival time and city. These times would be standardised to GMT and a
date difference calculated which would be the theoretical elapsed time
of the flight

I have one Table called Cities which has the name of the city and the
difference from GMT eg Sydney is 10 (ie 10 hrs ahead of GMT), LA is -7
ie 7 hours behind GMT.

I then have a second table called flights with data to record the
departure and arrival times (local ) of planned flights, using combo
boxes to choose the arrival and departure cities. I want the data to
calculate the elapsed time. I assume I will need a flight header
table and a flight detailed table to allow me to calculate when more
than one flight is involved but have never used time differential
calculations before so am looking for some basic starting points

Appreciate any help
Steve

[Steve]

On Jun 7, 7:19*pm, "Allen Browne" <[e-mail]:

There are a few issues here, Steve - such as what to store verses what to
display - and the best solution will depend on what you are trying to do
with your data.

An argument could be made for storing everything on the GMT clock, and then
displaying the date/time for the local timezone where appropriate. That
would really simplify any calculations you need to make, e.g. when a pilot
needs a break, when a maintenance needs to be done, which planes are in the
air at the same time, hours travelled, and how long flight times are.

One of the issues is knowing the history of times at the various locations
you must track. Daylight saving adjustments are not consistent throughout
the years (e.g. Sydney was different the year it held the Olympics.) You
therefore need either a table to record all time changes for all locations,
or some way to query that from the OS. (I think I read that Vista has that
history, but XP does not.)

Anyway, if you store everything on GMT, and you know when all the timezones
change, you can interface it to display local time. You can even use some
unbound controls overlaying the calculated local time to allow the user to
enter local times, and convert them to GMT times for storage. Post back if
you need more help on that.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve" <[e-mail] in message

news:8c**********************************@f24g2000 prh.googlegroups.com...

I am trying to calculate elapsed travel times for flights. *My plan is
to enter the local departure time, the departure city and the local
arrival time and city. *These times would be standardised to GMT and a
date difference calculated which would be the theoretical elapsed time
of the flight

I have one Table called Cities which has the name of the city and the
difference from GMT eg Sydney is 10 (ie 10 hrs ahead of GMT), LA is -7
ie 7 hours behind GMT.

I then have a second table called flights with data to record the
departure and arrival times (local ) of planned flights, using *combo
boxes to choose the arrival and departure cities. *I want the data to
calculate the elapsed time. *I assume I will need a flight header
table and a flight detailed table to allow me to calculate when more
than one flight is involved but have never used time differential
calculations before so am looking for some basic starting points

Appreciate any help
Steve- Hide quoted text -

- Show quoted text -

Allan,
Thanks for the interest
The idea of standardising on GMT was what I was pursuing. I had
thought of the daylight saving time issue but had decided to work the
time difference issue first as I had never done calcs with time and my
initial attempts failed. I'd appreciate any beginners advice there.
My first attempt was to enter local time on a form which also had a
combo for the town and its associated GMT offset, then use the
me.GMTdeparture=dateadd("h",me.cmobodeparture.colu mn(3) plus the
departure time to get a standard time (where column(3) is the GMT
offset. However the specific scripting required lost me and I havent
even thought about changes to a different day that the calculation
could require
Steve

[Allen Browne]

DateAdd() is the right approach.

I imagine you will have fields like this:
TakeOffDT Date/Time take-off as GMT date/time
Duration Number (Long) Duration of flight in minutes.
AircraftID foreign key to a table of aircraft
FromID foreign key to a table of locations
ToID foreign key to a table of locations

The location table would have fields:
LocationID AutoNumber primary key
LocationName Text city name
MinutesOffset Number Number of minutes offset from GMT.

You can now now calculate the local departure time with a calculated field
in a query that uses both tables:
LocalDepartTime: DateAdd("n", [MinutesOffset], [TakeOffDT])

This is a simplified design that doesn't cope with daylight saving, and also
doesn't handle milk-run type flights (where one flight stops at several
destinations.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve" <st**********@gmail.comwrote in message:

The idea of standardising on GMT was what I was pursuing. I had
thought of the daylight saving time issue but had decided to work the
time difference issue first as I had never done calcs with time and my
initial attempts failed. I'd appreciate any beginners advice there.
My first attempt was to enter local time on a form which also had a
combo for the town and its associated GMT offset, then use the
me.GMTdeparture=dateadd("h",me.cmobodeparture.colu mn(3) plus
the departure time to get a standard time (where column(3) is the GMT
offset. However the specific scripting required lost me and I havent
even thought about changes to a different day that the calculation
could require
Steve