Postgres - convert date from one format to another

Question

Question itself was asked multiple times but I happened to come across a problem I can't fix.

I Have a table containing dates in format: Tue Jan 29 15:24:20 CET 2019 which according to fields in documentation stands for Dy Mon DD HH24:MI:SS TZ YYYY.

updated column in table is type of text

I would like to update each row and convert this date to YYYY-MM-DD HH24:MI:SS but first I want to convert the date from one format to the other however I got sql error:

select to_char(to_date(updated, 'Dy Mon DD HH24:MI:SS TZ YYYY'), 'YYYY-MM-DD HH24:MI:SS') from audit where id =1267;
ERROR:  formatting field "TZ" is only supported in to_char

Can anyone give me a hint how to do so?

Never store date (or timestamp) values in text or varchar columns. If you had used a proper date data type, you could simply use to_char() to display the date in any format you wish. — user330315
– user330315, Commented Feb 14, 2019 at 8:25
@a_horse_with_no_name dont mention it to me.. I just happened to deal with the database "as it is".. Ofc no -XL - fixed link to doc — Mithrand1r
– Mithrand1r, Commented Feb 14, 2019 at 8:27

melpomene · Accepted Answer · 2019-02-16 12:02:18Z

3

One problem with your code is that to_date returns a date only (without a time). to_timestamp would be more appropriate.

But even then the documentation says:

TZ - upper case time-zone abbreviation (only supported in to_char)

But the documentation also says:

Tip:
to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. For most standard date/time formats, simply casting the source string to the required data type works, and is much easier.

So what you should do instead is to use a cast:

cast(updated as timestamp with time zone)

Or, if you want to ignore the timezone:

cast(updated as timestamp)

answered Feb 16, 2019 at 12:02

melpomene

86.2k8 gold badges97 silver badges156 bronze badges

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

Collectives™ on Stack Overflow

Postgres - convert date from one format to another

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