Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string

Question

I have raw text column with values like '2012-07-26T10:33:34' and '2012-07-26T10:56:16'. In Java using Joda-Time I can easily convert this to/from a date by calling

new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss").

In Postgres how would I ignore this 'T' character in the middle?

I have tried 'YYYY-MM-DD\THH:mm:ss' but get the error

> ERROR: failed to find conversion function from unknown to text

Answer 1

If I'm not mistaken the T is allowed in an ANSI ANSI timestamp literal, so the following should work.

select timestamp '2012-07-26T10:33:34';

You say you have a text column, so you probably need something like this:

create table foo (ts text);
insert into foo values ('2012-07-26T10:33:34')
select cast(ts as timestamp)
from foo;

This works as well:

select to_timestamp(ts, 'yyyy-mm-dd hh24:mi:ss')
from foo;

SQLFiddle example: http://sqlfiddle.com/#!12/0b369/1

Answer 2

As mentioned the CAST solution does work

SELECT CAST('2012-07-26T10:33:34' AS TIMESTAMP);

      timestamp
---------------------
 2012-07-26 10:33:34

but the TO_TIMESTAMP with just a space or a T

DOES NOT WORK

SELECT TO_TIMESTAMP('2012-07-26T10:33:34', 'YYYY-MM-DD HH24:MI:SS');

ERROR:  invalid value "T1" for "HH24"
DETAIL:  Value must be an integer.

NOR DOES THIS using a T

SELECT TO_TIMESTAMP('2012-07-26T10:33:34', 'YYYY-MM-DDTHH24:MI:SS');

      to_timestamp
------------------------
 2012-07-26 00:03:34+10

but surprisingly using lower case hh24 and an uppercase T

DOES WORK

SELECT TO_TIMESTAMP('2012-07-26T10:33:34', 'YYYY-MM-DDThh24:MI:SS');

      to_timestamp
------------------------
 2012-07-26 10:33:34+10