Re: to_timestamp alternatives

> On 01 Jan 2016, at 0:46, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote:
>
> BTW, my recommendation would be to store in a timestamptz field *with the correct timezone*, and then convert on output as 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 data sooner 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, either by 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-over dates. For example, India is currently at UTC+05:30, probably because they wrapped the entire country in the same TZ 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.