CODEWITHSUNDEEP
phppostgresql
aagjalpankaj
aagjalpankaj

Reputation: 1180

ERROR: invalid input syntax for type timestamp with time zone: "09/03/1943 01:00:00 MWT"

I am inserting records from table1.column1 to table2.colum2. Both the columns are of timestampz type.

Unfortunately, one record having value "1943-09-03 01:00:00-06" throws below error:

ERROR: invalid input syntax for type timestamp with time zone: "09/03/1943 01:00:00 MWT"

I can fix this by using ::TIMESTAMP.

Can anyone tell me what is the right way to fix it and why it is happening only for the one record?

Edit 1:

Both the tables are on different databases. So I am selecting values from Table1.column1 and then inserting into Table2.column2 separately in a different database connection. I guess, PHP's core PostgreSQL functions converting value "1943-09-03 01:00:00-06" to string time "09/03/1943 01:00:00 MWT" and this string time throwing error while inserting.

I am actually trying to insert "09/03/1943 01:00:00 MWT" and not "1943-09-03 01:00:00-06".

Upvotes: 3

Views: 16019

Answers (1)

D-Shih
D-Shih

Reputation: 46249

using ::TIMESTAMP will use DB default time format, the format depend on your DB culture.

I would suggest you specify the datetime format of your string explicitly When you want to convert string to DateTime or timestamp

you can try to use to_timestamp function and set string to DateTime format.

Query 1:

SELECT to_timestamp('09/03/1943 01:00:00 MWT', 'DD/MM/YYYY hh24:mi:ss')

Results:

|         to_timestamp |
|----------------------|
| 1943-03-09T01:00:00Z |

Upvotes: 2

Related Questions