How to convert this string into timestamp in postgresql?

Question

I want to convert a string into timestamp I am using the following command for it

SELECT To_timestamp ('12-31-2021-23-38-02', 'DD-MM-YYYY-HH24-MI-SS');

However it is returning me '2023-07-17 23:38:02' this instead of '2021-31-12 23:38:02'

जलजनक · Accepted Answer · 2022-03-12 15:42:34Z

1

Place of month & day in the input string doesn't match format string.

Use:

SELECT To_timestamp ('12-31-2021-23-38-02', 'MM-DD-YYYY-HH24-MI-SS');

As pointed out in the comments, it's apparent that you're using Postgres prior to version 10.

Since Postgres 10, your query throws below error:

ERROR:  date/time field value out of range: "12-31-2021-23-38-02"

dbfiddle.uk - Postgres 10

edited Mar 12, 2022 at 15:42

answered Mar 12, 2022 at 15:35

जलजनक

3,0812 gold badges26 silver badges30 bronze badges

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

3 Comments

user330315 Over a year ago

Since Postgres 10, the original expression results in "date/time field value out of range"

Anurag Patel Over a year ago

Hi, Thanks for the help I feel so embarrassed right now that I spent half an hour debugging it and was not able to notice that column had date in MM-DD format instead of DD-MM format T_T

जलजनक Over a year ago

Well, I can only recommend 5 mins break every 25 mins of coding.

Collectives™ on Stack Overflow

How to convert this string into timestamp in postgresql?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related