ProgrammingError at /admin/, date/time field value out of range

[Benedict Verheyen]

Hi,

on a Debian Etch system, i use Django svn (rev 8911) with Postgres 7.4.
Other components are Apache2 (v. 2.2.3-4) & mod_python.
I created the database with this command:
createdb -E ENCODING UNICODE -U postgres testdb

python manage syncdb runs without a problem.

I installed all components and started a site and activated the admin
site. However, when i go to the admin page, i get this error:

ProgrammingError at /admin/

date/time field value out of range: "2008-09-03 02:14:14.523642"

Request Method: GET
Request URL: http://djangotest.heimdallit.be:8891/admin/
Exception Type: ProgrammingError
Exception Value:

date/time field value out of range: "2008-09-03 02:14:14.523642"

Exception Location:
/usr/lib/python2.4/site-packages/django/db/backends/util.py in execute,
line 19
Python Executable: /usr/bin/python
Python Version: 2.4.4
Python Path: ['/usr/local/django-apps/', '/usr/lib/python24.zip',
'/usr/lib/python2.4', '/usr/lib/python2.4/plat-linux2',
'/usr/lib/python2.4/lib-tk', '/usr/lib/python2.4/lib-dynload',
'/usr/local/lib/python2.4/site-packages',
'/usr/lib/python2.4/site-packages']
Server time: Wed, 3 Sep 2008 02:14:14 -0500

...

# /usr/lib/python2.4/site-packages/django/db/backends/util.py in execute

12. def __init__(self, cursor, db):
13. self.cursor = cursor
14. self.db = db # Instance of a BaseDatabaseWrapper subclass
15.
16. def execute(self, sql, params=()):
17. start = time()
18. try:

19. return self.cursor.execute(sql, params)

params
('13c4205a204dcbe96e58a8bb9b24bb54', u'2008-09-03 02:09:10.713320')
self
<django.db.backends.util.CursorDebugWrapper object at 0x4e6db74c>
sql
'SELECT "django_session"."session_key", "django_session"."session_data",
"django_session"."expire_date" FROM "django_session" WHERE
("django_session"."session_key" = \'13c4205a204dcbe96e58a8bb9b24bb54\'
AND "django_session"."expire_date" > \'2008-09-03 02:09:10.713320\' )'
start
1220425750.7437551
stop
1220425750.7497211

Any ideas?

Thanks,
Benedict

[Chubby]

On 3 sep, 09:25, Benedict Verheyen <benedict.verhe...@gmail.com>
wrote:

I forgot to mention the following. When i execute the sql statement
from above directly
on the database, it works. No error what so ever.
So the db configuration would appear to be correct.

What could be causing this error ?

Thanks,
Benedict

[Benedict Verheyen]

Chubby schreef:

>
>
> I forgot to mention the following. When i execute the sql statement
> from above directly
> on the database, it works. No error what so ever.
> So the db configuration would appear to be correct.
>
> What could be causing this error ?
>
> Thanks,
> Benedict

When i use wrong dates in a query and execute that directly on the database,
then i get the same error message.
Judging from the error message, it seems as if the date is in a different
format than expected by the database.

Any idea of how i can further trace this problem as i'm really stuck here.
I haven't even written any code yet so it's a config problem or a bug.

Thanks for any idea's,
Regards,
Benedict

[ekellner]

When i use wrong dates in a query and execute that directly on the database,
then i get the same error message.
Judging from the error message, it seems as if the date is in a different
format than expected by the database.

The expected date format is a per-connection level setting. (Called "DATESTYLE" in postgresql).  There is also a default format if the connection doesn't specify.  

I strongly suspect that this mismatch between formats is actually happening between the database and the python postgresql driver, and does not involve django.

You should be able to test this my executing a "SHOW DATESTYLE" using django's db connection, and also in the working client, and comparing.  Another third thing is to open a python shell, create a connection, and execute the sql there and see if that works. 

I don't have a postgres database currently so I can't be certain about this, but that's where I would start investigating.  

[Benedict Verheyen]

ekellner schreef:

> The expected date format is a per-connection level setting. (Called
> "DATESTYLE" in postgresql). There is also a default format if the
> connection doesn't specify.
>
> I strongly suspect that this mismatch between formats is actually
> happening between the database and the python postgresql driver, and
> does not involve django.
>
> You should be able to test this my executing a "SHOW DATESTYLE" using
> django's db connection, and also in the working client, and comparing.
> Another third thing is to open a python shell, create a connection, and
> execute the sql there and see if that works.
>
> I don't have a postgres database currently so I can't be certain about
> this, but that's where I would start investigating.

Thanks for the info.
I'm seems i'm unable to set the correct datestyle to get this to work.

When i run it from a python shell, i do indeed get the same error:

python manage shell
>>> from django.db import connection, backend
>>> cursor = connection.cursor()
>>> sql = 'SELECT "django_session"."session_key",

"django_session"."session_data", "django_session"."expire_date" FROM
"django_session" WHERE ("django_session"."session_key" =

\'996ac580f43dbf6f57028f13ae1537ba\' AND "django_session"."expire_date" >
\'2008-09-05 00:09:52.087243\' )'
>>> cursor.execute(sql)
Traceback (most recent call last):
File "<console>", line 1, in ?
File "/usr/lib/python2.4/site-packages/django/db/backends/util.py", line
19, in execute
return self.cursor.execute(sql, params)
ProgrammingError: date/time field value out of range: "2008-09-05 00:09:52.087243"

In my postgres.conf, the datestyle is defined as this:
datestyle = 'iso, mdy'

On the database (psql webshop)
webshop=# SHOW DATESTYLE;
DateStyle
-----------
ISO, MDY
(1 row)

Because the datestyle in the sql statement is passes as Ymd, i tried to set
the datestyle in the postgres.conf file als datestyle = 'iso, ymd' and
datestyle = 'sql, us' but they all resulted in the same error.

Any ideas as to what i need to set datesytle to get it to work?

Thanks,
Benedict

[Benedict Verheyen]

Benedict Verheyen schreef:

I did some more testing
When i execute the sql from the psql command line, the first time it gives an
error, the 2nd time it doesn't ?! How weird is that :)
Date style is set to 'iso, ymd'

psql webshop
Welcome to psql 7.4.19, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
webshop=# SELECT "django_session"."session_key",

"django_session"."session_data", "django_session"."expire_date" FROM
"django_session" WHERE ("django_session"."session_key" =
'996ac580f43dbf6f57028f13ae1537ba' AND "django_session"."expire_date" >

'2008-09-05 00:09:52.087243' );
ERROR: date/time field value out of range: "2008-09-05 00:09:52.087243"
webshop=# SELECT "django_session"."session_key",

"django_session"."session_data", "django_session"."expire_date" FROM
"django_session" WHERE ("django_session"."session_key" =
'996ac580f43dbf6f57028f13ae1537ba' AND "django_session"."expire_date" >

'2008-09-05 00:09:52.087243' );
session_key | session_data | expire_date
-------------+--------------+-------------
(0 rows)

This is very strange.

Any more ideas?

Regards,
Benedict

[Benedict Verheyen]

Benedict Verheyen schreef:

Sorry for my many posts but i really need to get this solved.

On more observation. From a django shell, this statement works:

>>> sql = 'SELECT "django_session"."session_key",
"django_session"."session_data", "django_session"."expire_date" FROM
"django_session" WHERE ("django_session"."session_key" =
\'996ac580f43dbf6f57028f13ae1537ba\' AND "django_session"."expire_date" >

\'05-09-2008 00:09:52.087243\' )'

Look at the date, i changed the date into a dmY format here instead of Ymd.
This works !

Is there anyway i can force Django to present dates to the database backend as
dmY instead of Ymd. Setting DATE_FORMAT and DATETIME_FORMAT doesn't seem to
have an effect on this.

Regards,
Benedict

[ekellner]

On Fri, Sep 5, 2008 at 1:41 AM, Benedict Verheyen <benedict...@gmail.com> wrote:

Because the datestyle in the sql statement is passes as Ymd, i tried to set 

the datestyle in the postgres.conf file als datestyle = 'iso, ymd' and  

datestyle = 'sql, us' but they all resulted in the same error.

Does postgres cache the settings into a database table?  Is there some kind of flush command?  I am surprised that this didn't work.  This should have been the thing that you needed to do to fix it.  Maybe there's a bug in postgres (seems unlikely), or you've done something wrong.  I would still work on this front, because this fix is going to be far, far easier to implement and maintain than the alternatives.

Is there anyway i can force Django to present dates to the database backend as
dmY instead of Ymd. Setting DATE_FORMAT and DATETIME_FORMAT doesn't seem to
have an effect on this.

I suspect not, but you can dive into the django code to check.  

You might be able to hook into the database connection routines, and do a SET DATESTYLE on each connection.  You probably can get away with doing this in your application code on each connection, and avoid making changes to the Django source; although you do have to then mind django's connection management a bit.  It's not efficient, it's not the right way to do it, but for testing it could work.