On 06/26/2015 11:41 AM, litu16 wrote:
I know how to convert a text to timestamp in postgreSQL using
* SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY')*
but how can I convert a text variable (inside a function) to timestamp??
In my table (table_ebscb_spa_log04) "time" is a character varying column, in
which I have placed a formated date time (15-11-30 11:59:59.999 PM).
I have tried this function, in order to convert put the date time text into
a variable (it always change) and convert it into timestamp...
* CREATE OR REPLACE FUNCTION timediff()
RETURNS trigger AS
$BODY$
DECLARE
timeascharvar character varying;
timeastistamp timestamp;
BEGIN
IF NEW.time_type = 'Lap' THEN
SELECT t.time FROM table_ebscb_spa_log04 t INTO timeascharvar;
SELECT to_timestamp('timeascharvar', 'yy-mm-dd HH24:MI:SS.MS') INTO
Lose the quotes on timeascharvar, it is a string already.
So:
SELECT to_timestamp(timeascharvar, 'yy-mm-dd HH24:MI:SS.MS')
timeastistamp;
END IF;
RETURN timeastistamp;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION timediff()
OWNER TO postgres;*
but whenever I run it in the table, it shows this ERROR message...
<http://postgresql.nabble.com/file/n5855346/screenshot.bmp>
I click on the link above I get:
PostgreSQL
File not found
Please contact Nabble Support if you need help.
It seems that "to_timestamp" waits for a number to be the year, how can I
get it to recognize the variable as if it were numbers?
--
View this message in context: http://postgresql.nabble.com/How-to-convert-a-text-variable-into-a-timestamp-in-postgreSQL-tp5855346.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
