opengeotracker-devel

[Opengeotracker-devel] how do we handle timezones ?

[Tom V. B. <tom...@gm...>]

Dear list,

I'm going to Turkey in a few days, which is in a different timezone, and I
noticed that we currently don't keep track of timezone information in the
database, since MySQL does not allow this.

Has there been some thought on how we should handle this ?

The best way (I think) is to always store DATETIME's in the GMT timezone,
and then convert them to the user's timezone when outputting the DATETIME
for viewing.

Any thoughts on this ?

Best regards,

Tom.

Re: [Opengeotracker-devel] how do we handle timezones ?

[Johan E. <jk...@ek...>]

2010-08-25 16:57, Tom Van Braeckel skrev:
> Dear list,
>
> I'm going to Turkey in a few days, which is in a different timezone, and
> I noticed that we currently don't keep track of timezone information in
> the database, since MySQL does not allow this.
>
> Has there been some thought on how we should handle this ?
>
> The best way (I think) is to always store DATETIME's in the GMT
> timezone, and then convert them to the user's timezone when outputting
> the DATETIME for viewing.
>
> Any thoughts on this ?
>

logMany.php receives the time in ISO 8601 format, for example: 
2010-08-25T17:05:11+01:00

The easiest way is if you can retrieve your current timezone and adjust 
the "+01:00" in the example above to the right thing. (For example if 
your timezone is "GMT+05" adjust to "+05:00" before sending to server.

Another way is to look up the timezone (a rest api) from your current 
location (lat,lng). Normally you only need to do this when you start 
logging (once). You can use for example this api:

http://ws.geonames.org/timezone?lat=45.5555555&lng=22.222222

(as I'm using in trunk/server/keyInfo.php)

   //regards: Johan

Re: [Opengeotracker-devel] how do we handle timezones ?

[Tom V. B. <tom...@gm...>]

On Wed, Aug 25, 2010 at 5:13 PM, Johan Ekblad <jk...@ek...> wrote:

> 2010-08-25 16:57, Tom Van Braeckel skrev:
> > Dear list,
> >
> > I'm going to Turkey in a few days, which is in a different timezone, and
> > I noticed that we currently don't keep track of timezone information in
> > the database, since MySQL does not allow this.
> >
> > Has there been some thought on how we should handle this ?
> >
> > The best way (I think) is to always store DATETIME's in the GMT
> > timezone, and then convert them to the user's timezone when outputting
> > the DATETIME for viewing.
> >
> > Any thoughts on this ?
> >
>
> logMany.php receives the time in ISO 8601 format, for example:
> 2010-08-25T17:05:11+01:00
>
> The easiest way is if you can retrieve your current timezone and adjust
> the "+01:00" in the example above to the right thing. (For example if
> your timezone is "GMT+05" adjust to "+05:00" before sending to server.
>
> Another way is to look up the timezone (a rest api) from your current
> location (lat,lng). Normally you only need to do this when you start
> logging (once). You can use for example this api:
>
> http://ws.geonames.org/timezone?lat=45.5555555&lng=22.222222
>
> (as I'm using in trunk/server/keyInfo.php)
>

Thanks, but that's not what I meant.
Knowing the timezone in Java is not problem (I use
Calendar.getInstance().get(Calendar.TIMEZONE_OFFSET).

The problem is that our database does not keep track of the time zone (in
the "time" column of PositionLog) so I proposed always converting the time
to GMT+00:00 time before storing it. Maybe this convention has been followed
before, but I wasn't sure so if you approve, I will always convert the
(local) time to GMT+00:00 before storing in in the database...



>
>   //regards: Johan
>
>
> ------------------------------------------------------------------------------
> Sell apps to millions through the Intel(R) Atom(Tm) Developer Program
> Be part of this innovative community and reach millions of netbook users
> worldwide. Take advantage of special opportunities to increase revenue and
> speed time-to-market. Join now, and jumpstart your future.
> http://p.sf.net/sfu/intel-atom-d2d
> _______________________________________________
> Opengeotracker-devel mailing list
> Ope...@li...
> https://lists.sourceforge.net/lists/listinfo/opengeotracker-devel
>

Re: [Opengeotracker-devel] how do we handle timezones ?

[Johan E. <jk...@ek...>]

2010-08-25 18:49, Tom Van Braeckel skrev:
> On Wed, Aug 25, 2010 at 5:13 PM, Johan Ekblad <jk...@ek...
> <mailto:jk...@ek...>> wrote:
>
>     2010-08-25 16:57, Tom Van Braeckel skrev:
>      > Dear list,
>      >
>      > I'm going to Turkey in a few days, which is in a different
>     timezone, and
>      > I noticed that we currently don't keep track of timezone
>     information in
>      > the database, since MySQL does not allow this.
>      >
>      > Has there been some thought on how we should handle this ?
>      >
>      > The best way (I think) is to always store DATETIME's in the GMT
>      > timezone, and then convert them to the user's timezone when
>     outputting
>      > the DATETIME for viewing.
>      >
>      > Any thoughts on this ?
>      >
>
>     logMany.php receives the time in ISO 8601 format, for example:
>     2010-08-25T17:05:11+01:00
>
>     The easiest way is if you can retrieve your current timezone and adjust
>     the "+01:00" in the example above to the right thing. (For example if
>     your timezone is "GMT+05" adjust to "+05:00" before sending to server.
>
>     Another way is to look up the timezone (a rest api) from your current
>     location (lat,lng). Normally you only need to do this when you start
>     logging (once). You can use for example this api:
>
>     http://ws.geonames.org/timezone?lat=45.5555555&lng=22.222222
>     <http://ws.geonames.org/timezone?lat=45.5555555&lng=22.222222>
>
>     (as I'm using in trunk/server/keyInfo.php)
>
>
> Thanks, but that's not what I meant.
> Knowing the timezone in Java is not problem (I use
> Calendar.getInstance().get(Calendar.TIMEZONE_OFFSET).
>
> The problem is that our database does not keep track of the time zone
> (in the "time" column of PositionLog) so I proposed always converting
> the time to GMT+00:00 time before storing it. Maybe this convention has
> been followed before, but I wasn't sure so if you approve, I will always
> convert the (local) time to GMT+00:00 before storing in in the database...
>

Currently logManyPos.php just do:

$mytime=date('Y-m-d H:i:s',strtotime($iso8601stringFromAndroid));

So it gets converted to the local time for the server. So if the server 
have GMT+01 (as it currently have) all times will be saved so.

When displaying times we know the lat,lng for the data and can convert 
back to the users timezone again.