Search Postgresql Archives

Re: to_timestamp alternatives

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux