How to convert a text variable into a timestamp in postgreSQL?

Question

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

enter image description here

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?

Eelke · Accepted Answer · 2015-06-26 18:49:34Z

1

The first parameter to to_timestamp should be your var not a string containing the name of your var:

to_timestamp(timeascharvar, 'yy-mm-dd HH24:MI:SS.MS')

answered Jun 26, 2015 at 18:49

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert a text variable into a timestamp in postgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related