Re: to_timestamp alternatives

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. 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.