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

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

<http://postgresql.nabble.com/file/n5855346/screenshot.bmp>

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.

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

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@aklaver.com