Re: to_timestamp alternatives

On 12/31/2015 01:34 PM, gkhan wrote:
> Thanks very much for both of your replies.  I had tried something similar and
> gotten an error, so I am probably making a stupid mistake.  If I try this,
> it works:
>
>     SELECT ('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS')::timestamp
>
> but if I use column names instead of the text, like this, it fails:
>     SELECT (gmt_date||' '||lpad(gmt_time),'DD.MM.YYYY HH24:MI:SS')::timestamp
> ...
>
> Both the gmt_date and gmt_time columns are "text" data type and formatted
> exactly as in the original example, but I get this error:
>     ERROR:  cannot cast type record to timestamp without time zone
>
> I certainly would prefer "doing things in the multiple ways that will work,"
> and I have probably just missed the obvious solution.  I work on wildlife
> telemetry, and most GPS units and other satellite-linked devices report
> times in UTC.  We often run into problems where someone plugs a laptop into
> a piece of equipment and downloads data, and inadvertently sets the times to
> a local time zone.  Therefore, we try to stick to UTC whenever collating
> data from different sources.  However, when studying wildlife activity
> patterns we are interested in local, biologically-meaningful times such as
> sunrise and sunset, but not in daylight savings times, which are meaningless
> to wildlife.

But not necessarily to the interaction of wildlife with humans. As
someone who used to drive for a living I can tell you DST/ST moving
human activity backwards and forwards through biologically-meaningful
times has meaning to the critters.

> Therefore, most of us just add a fixed interval to UTC to
> represent "local" times.
>
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723p5879738.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com