> On 01 Jan 2016, at 0:46, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:
>
> BTW, my recommendation would be to store in a timestamptz field *with the correct timezone*, and then convert on
outputas necessary. This is easy to do by either
>
> SET timezone
>
> or
>
> SELECT timestamptz_field AT TIME ZONE '…';
This. When converting the original timestamps to UTC, you lose data. In my experience, you will end up needing that
datasooner or later.
Remember, the database stores timestamps in UTC internally anyway, you don't need to the conversion yourself.
> Since you're dealing with GPS data and presumably have lat/long, it shouldn't be hard to do this dynamically either,
eitherby just blindly dividing longitude by 15 or using actual timezone shape polygons and @> or <@.
That would be a bad idea for global data, since not all time zones are full hours apart, or have the same (if any) DST
change-overdates. For example, India is currently at UTC+05:30, probably because they wrapped the entire country in the
sameTZ after their independence.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.