Thread: ERROR: invalid input syntax for type timestamp with time zone
Hi All,
Regards,
Rushabh Lathia
Consider the following test:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit
(1 row)
postgres=# create table test ( a timestamptz);
CREATE TABLE
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
INSERT 0 1
postgres=# select * from test;
a
------------------------------
1000-03-12 03:52:16+05:53:28
(1 row)
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar 11 23:58:48 10000 IST"
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
So, if you see while trying to insert date 'Sat Mar 11 23:58:48 10000 IST' , its returns with invalid input syntax. Error coming from the stack (timestamptz_in() -> DecodeDateTime()).
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit
(1 row)
postgres=# create table test ( a timestamptz);
CREATE TABLE
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
INSERT 0 1
postgres=# select * from test;
a
------------------------------
1000-03-12 03:52:16+05:53:28
(1 row)
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar 11 23:58:48 10000 IST"
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
So, if you see while trying to insert date 'Sat Mar 11 23:58:48 10000 IST' , its returns with invalid input syntax. Error coming from the stack (timestamptz_in() -> DecodeDateTime()).
But when I tried to enter same date with other format then it does work.
postgres=# insert into test values ( '10000-03-11 23:58:48');
INSERT 0 1
postgres=# select * from test;
a
-------------------------------
Sat Mar 11 23:58:48 10000 IST
(1 row)
INSERT 0 1
postgres=# select * from test;
a
-------------------------------
Sat Mar 11 23:58:48 10000 IST
(1 row)
Looking at the code, it seems like for Postgres,MDY datestyle DecodeDateTime() doesn't handle date properly if year is greater then 4 digits (greater then 9999). Do you see this as bug or expected output ?
Rushabh Lathia