Thread: date/time out of range

This error confuses me:

10127 Caused by: org.postgresql.util.PSQLException: ERROR: date/time
field value out of range: "2011-01-12 21:30:00.000000 +13:00:00"

The date appears to be  valid,

 smx3_mailreport=# select timestamp '2011-01-12 21:30:00.000000 +13:00:00';
      timestamp
---------------------
 2011-01-12 21:30:00
(1 row)


I cant see how any session paramater would affect this?

OSX - Postgres 8.4.4 , jdbc 8.4.702

On 18/01/11 17:00, Colin Taylor wrote:
> This error confuses me:
>
> 10127 Caused by: org.postgresql.util.PSQLException: ERROR: date/time
> field value out of range: "2011-01-12 21:30:00.000000 +13:00:00"

You should show us the code that generates this error, and your schema.

Oliver

Please remember to cc: the list when you reply.

On 18/01/11 18:01, Colin Taylor wrote:
> On Tue, Jan 18, 2011 at 5:20 PM, Oliver Jowett<oliver@opencloud.com>  wrote:
>> On 18/01/11 17:00, Colin Taylor wrote:
>>>
>>> This error confuses me:
>>>
>>> 10127 Caused by: org.postgresql.util.PSQLException: ERROR: date/time
>>> field value out of range: "2011-01-12 21:30:00.000000 +13:00:00"
>>
>> You should show us the code that generates this error, and your schema.
>>
>> Oliver
>>
>
> select id, name, address_type_id, default_timezone, customer_ref,
> from_date from domain where
> (from_date<= '2011-01-12 21:30:00.000000 +13:00:00' or from_date is
> null) and (thru_date is null or
> thru_date>  '2011-01-12 21:30:00.000000 +13:00:00')   and
> address_type_id = '10' and name in ('ALL','<>');

Well.. that's not JDBC code, is it? If I run that in psql against 8.4.5,
it runs just fine and I see no error. How do you actually construct and
execute that query via JDBC?

We need a selfcontained test case that compiles and runs "out of the
box" showing the problem. Otherwise I don't think anyone can help you here.

Oliver

Samuel Gendler wrote:

> Is there a +13:00:00 timezone or does that wind up crossing the date
> line? If Java has no mechanism for converting that time zone to whatever
> date/time type it is trying to convert to, the exception would make sense.

+1300 is fine (NZ in summer - I'm there now!)
The Chatham Islands are even further east while still on the same side
of the dateline, they're +1345 at the moment.

Older server versions had problems with timezones beyond +1200, but that
was fixed ages ago (7.x era)

Oliver

Samuel Gendler <sgendler@ideasculptor.com> writes:
>> On 18/01/11 17:00, Colin Taylor wrote:
>>> 10127 Caused by: org.postgresql.util.PSQLException: ERROR: date/time
>>> field value out of range: "2011-01-12 21:30:00.000000 +13:00:00"

> Is there a +13:00:00 timezone or does that wind up crossing the date line?
> If Java has no mechanism for converting that time zone to whatever date/time
> type it is trying to convert to, the exception would make sense.

The weird thing is that the message appears to be a server-side error;
at least it exactly matches the spelling of one server error.  But I
don't understand how come it occurs only from JDBC and not in psql.

            regards, tom lane

Oops I'm an idiot of course this is server side only.

For more background I'm running a java log processing system using
straight JDBC, I will get logs error out occasionally with this error
and then rerun them and its all good! As the process is multi-threaded
runs aren't idempotent but this particular query would always occur.

I cant reproduce this problem on redhat only OSX so its good that I
dont get in production only on my dev box..


On Wed, Jan 19, 2011 at 5:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Samuel Gendler <sgendler@ideasculptor.com> writes:
>>> On 18/01/11 17:00, Colin Taylor wrote:
>>>> 10127 Caused by: org.postgresql.util.PSQLException: ERROR: date/time
>>>> field value out of range: "2011-01-12 21:30:00.000000 +13:00:00"
>
>> Is there a +13:00:00 timezone or does that wind up crossing the date line?
>> If Java has no mechanism for converting that time zone to whatever date/time
>> type it is trying to convert to, the exception would make sense.
>
> The weird thing is that the message appears to be a server-side error;
> at least it exactly matches the spelling of one server error.  But I
> don't understand how come it occurs only from JDBC and not in psql.
>
>                        regards, tom lane
>

> Oops I'm an idiot of course this is server side only.

You showed a successful parsing of the very same timestamp through
psql in your original message--was that on the production server? Does
that exact statement fail through psql on your OS X box? If not, then
it does seem to be jdbc related, no? Can you capture protocol traffic
through DriverManager.setLogWriter() and setting loglevel in the jdbc
URL to help track this down?

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com