From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | gkhan <drjohnpayne(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: to_timestamp alternatives |
Date: | 2015-12-31 22:34:58 |
Message-ID: | 5685AD92.3020808@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | gkhan | 2015-12-31 23:05:34 | Re: to_timestamp alternatives |
Previous Message | gkhan | 2015-12-31 21:53:03 | Re: to_timestamp alternatives |