how to convert YYYYMMDDHHMMSS to 2013-11-24 11:29:07 in postgres

Question

Postgres char(14) fields stores datetime withou separators like 20131124112907

How to add separators so that result is string in format

2013-11-24 11:29:07

I tried Denis answer using

to_timestamp(timestamp, 'YYYYMMDDHHMISS')::text as updated_at

but got exception

  Message=ERROR: 22007: hour "15" is invalid for the 12-hour clock
  Code=22007

  File=src\backend\utils\adt\formatting.c
  Hint=Use the 24-hour clock, or give an hour between 1 and 12.

are you doing this conversion in sql-server ? – M.Ali Nov 24 '13 at 20:32 — M.Ali, Nov 24 '13 at 20:32

Denis de Bernardy · Accepted Answer · 2013-11-24T21:47:33.107

12

You can convert it using to_date() or to_timestamp():

select to_timestamp('20131124112907', 'YYYYMMDDHH24MISS')

http://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

edited Nov 24 '13 at 21:47

answered Nov 24 '13 at 20:46

Denis de Bernardy

75,850
13
131
154

I tried it but got exception. It looks like this requires 12hr clock. I updated question. How to fix? – Andrus Nov 24 '13 at 21:21
3

select to_timestamp('20131124154907', 'YYYYMMDDHH24MISS'); – Pavel Stehule Nov 24 '13 at 21:46

how to convert YYYYMMDDHHMMSS to 2013-11-24 11:29:07 in postgres

1 Answers1