CODEWITHSUNDEEP
postgresql
benstpierre
benstpierre

Reputation: 33591

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

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

Upvotes: 3

Views: 3036

Answers (3)

Kirill
Kirill

Reputation: 1

I have tried to make a space between T and following HH24 and it also works: select TO_TIMESTAMP('2022-11-09T06:14:00+03', 'YYYY-MM-DDT HH24:MI:SS');

to_timestamp
2022-11-09 06:14:00.000 +0300

Upvotes: 0

Michael Milewski
Michael Milewski

Reputation: 156

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

Upvotes: 1

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions