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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
