MS SQL date vs. datetime problem

[Jakub Labath]

Hi

As mentioned, I'm working on Microsoft SQL back end using pymssql.
It's going OK and I'm down to 12 errors in runtests.py.
But I hit a problem I could use some help with.

best shown by code

my model in dateapp is
# Create your models here.
class MyDate(meta.Model):
day=meta.DateField()

now follows the session from python interpreter

>>> from django.models.dateapp import mydates
>>> d = mydates.MyDate(day=datetime.date(2005,10,22))
>>> d.save()
>>> d.id
1
>>> d.day
datetime.date(2005, 10, 22)
>>> #now i just get the same record as e
>>> e = mydates.get_object(pk=1)
>>> e.day
datetime.datetime(2005, 10, 22, 0, 0)

oops
I put in date and I get datetime back.

Now It's obvious something is causing the code to retrieve it as
datetime instead of date.

What is my best course of action? Where to start looking? Is it the
driver? Is it my back end file? Something else?

I'm thinking if I could do a mind melt with somebody, who knows how
django is reading stuff from db and how does it know which field
should be which type, that would be ideal :-).
But I will be grateful for any info or advice that can help me.

jakub

[Jonathan Daugherty]

# oops
# I put in date and I get datetime back.

But DateTime objects can be used to represent both; you just have a
DateTime object with no time values.

--
Jonathan Daugherty
http://www.parsed.org

[Jakub Labath]

Hi

>
> But DateTime objects can be used to represent both; you just have a
> DateTime object with no time values.

Yes true, but it creates an error when running unittests, and I'm
assuming the tests are in place for a reason.

Best
jakub

[Adrian Holovaty]

On 10/22/05, Jakub Labath <jla...@gmail.com> wrote:
> my model in dateapp is
> # Create your models here.
> class MyDate(meta.Model):
> day=meta.DateField()
>
> now follows the session from python interpreter
>
> >>> from django.models.dateapp import mydates
> >>> d = mydates.MyDate(day=datetime.date(2005,10,22))
> >>> d.save()
> >>> d.id
> 1
> >>> d.day
> datetime.date(2005, 10, 22)
> >>> #now i just get the same record as e
> >>> e = mydates.get_object(pk=1)
> >>> e.day
> datetime.datetime(2005, 10, 22, 0, 0)

> [...]

> What is my best course of action? Where to start looking? Is it the
> driver? Is it my back end file? Something else?

What you need to do is make sure the driver typecasts datetime
database fields to Python datetime.date objects, and date database
fields to Python datetime.date objects. Some database drivers do this
automatically, but others provide hooks that let programmers use
custom typecasts. Each database driver is different. :-/

Let me know if this makes sense...

Adrian
--
Adrian Holovaty
holovaty.com | djangoproject.com | chicagocrime.org

[Jeremy Dunck]

On 10/22/05, Adrian Holovaty <holo...@gmail.com> wrote:
> What you need to do is make sure the driver typecasts datetime
> database fields to Python datetime.date objects, and date database
> fields to Python datetime.date objects. Some database drivers do this
> automatically, but others provide hooks that let programmers use
> custom typecasts. Each database driver is different. :-/
>
> Let me know if this makes sense...

The existing MySql backend has a good example of this; look at the
DATA_TYPES dict.

[Jakub Labath]

On 10/22/05, Adrian Holovaty <holo...@gmail.com> wrote:
>

> What you need to do is make sure the driver typecasts datetime
> database fields to Python datetime.date objects, and date database
> fields to Python datetime.date objects. Some database drivers do this
> automatically, but others provide hooks that let programmers use
> custom typecasts. Each database driver is different. :-/
>
> Let me know if this makes sense...

Yes it does. I saw some typecasting stuff in other backends and thought
that could be it. Now I know for sure :-). Thanks guys.

[Jakub Labath]

Hi,

I finally got around to look into this further. Here is Adrian's advice.

On 10/22/05, Adrian Holovaty <holo...@gmail.com> wrote:

> What you need to do is make sure the driver typecasts datetime
> database fields to Python datetime.date objects, and date database
> fields to Python datetime.date objects. Some database drivers do this
> automatically, but others provide hooks that let programmers use
> custom typecasts. Each database driver is different. :-/
>

Now pymssql driver doesn't do either. There is currently no
functionality to register your own conversion hooks, pymssql is
currently very simple wrapper around freetds.
However it gets worse even if such functionality was available, how
will the poor driver be able to tell when we have a date field and
when datetime field. This is all because MS SQL has only one data type
that does everything that deals with date or time - datetime.
So it doesn't mater if you are storing time or date or both it's all
datetime to MS SQL.

All the conversions that I have found in other backends are using the
db driver to do this (save for ado_mssql which i think must have the
same problem as pymssql).

Is there any way to register data type conversions in django code itself?

Because the way I see this django is the only one in the process that
knows when that datetime field is date when it is time and when it is
datetime.

Any help much appreciated.

Best
jakub

[Adrian Holovaty]

On 11/10/05, Jakub Labath <jla...@gmail.com> wrote:
> All the conversions that I have found in other backends are using the
> db driver to do this (save for ado_mssql which i think must have the
> same problem as pymssql).
>
> Is there any way to register data type conversions in django code itself?

The ado_mssql backend, which uses adodbapi, does indeed convert
dates/times to Python datetime objects. Can you give it a shot and see
how well the driver works? Progress has stopped on testing the
ado_mssql backend, and I'd like that to get stabilized.

[Jakub Labath]

Hi,

On 11/11/05, Adrian Holovaty <holo...@gmail.com> wrote:
> The ado_mssql backend, which uses adodbapi, does indeed convert
> dates/times to Python datetime objects. Can you give it a shot and see
> how well the driver works? Progress has stopped on testing the
> ado_mssql backend, and I'd like that to get stabilized.
>

Test ado_mssql? Ouch, now you are making me work in windows. But I am
willing to make that sacrifice for the greater good of django :-). I
will run the same test that i have on top of this thread there. I
expect the same result.

Three things to stabilize SQL support for either ado_mssql or pymssql
1. quote_name
2. Limit/Offset
3. Datetime/Time/Date

1. quote_name (easy one)
MS SQL uses [] to escape words that may be qualifiers e.g. if table
name is user the statement must look like this "select * from [user]"
I haven't submitted patch yet but was planning to

2. Limit/Offset
Microsoft has neither. It implements limit functionality by using
clause TOP, and it lacks offset altogether.
Furthermore top must be at the beginning of sql statement.
Postgres/Mysql/etc: select * from mytable limit 3
MSSQL: select top 3 * from mytable
There is no "top" clause in django/core/db code so the way gheorghe
and i got it to work is by simply checking it db engine is ado_mssql
and rewriting the query (which works but is ugly)
Now with offset , what gheorghe did is again check if the engine is ms
sql and rewrite the entire statement to use a subquery to achieve the
offset effect.

3. MS SQL doesn't have time field it doesn't have date field either
all it has is datetime
so we are forced to use datetime fields to store all three
date/time/datetime in mssql datetime. This works OK up to the moment
that when we are reading it back from db.

E.g. pymssql sees datetime field and returns python's
datetime.datetime() the driver has no chance to know that we have only
date info in that datetime field.
In other words application (django in this case) has to know to cast
datetime.datetime to datetime.date.

Those are the issues that I see for either of the mssql backends.

I'll work on this further and let you know.

jakub

[Eugene Lazutkin]

I want to highlight MSSQL problems outlined by Jacub below:

1) Yep, it should be [name].
2) In general you have to use an intermediate table. It is not as bad as it
sounds performance-wise, but it is a hassle. Or you have to design your
databases to simulate limit/offset functionality. In real life application
designers do the latter.
3) There are actually 2: datetime and smalldatetime. The difference between
them is not interesting for us:

datetime: Date and time data from January 1, 1753, through December 31,
9999, with an accuracy of three-hundredths of a second, or 3.33
milliseconds.
smalldatetime: Date and time data from January 1, 1900, through June 6,
2079, with an accuracy of one minute.

There are functions to extract time or date from them, but you cannot do it
by column type alone --- it should be requested explicitly.

Basically it boils down to that: in order to implement #1 and #2 we have to
have some level of abstraction, or we should be very clever transforming sql
statements from one format to another. In order to implement #3, fields
should know their type during creation of sql statements. If Django wants to
support MSSQL, some level of cooperation from Django will be required,
unless we want to use some stupid tricks, like sql rewriting, and encoding
column type in column name.

Thanks,

Eugene


"Jakub Labath" <jla...@gmail.com> wrote in
message news:252daa430511110917n52...@mail.gmail.com...

[Robert Wittams]

Eugene Lazutkin wrote:
>
> Basically it boils down to that: in order to implement #1 and #2 we have to
> have some level of abstraction, or we should be very clever transforming sql
> statements from one format to another. In order to implement #3, fields
> should know their type during creation of sql statements. If Django wants to
> support MSSQL, some level of cooperation from Django will be required,
> unless we want to use some stupid tricks, like sql rewriting, and encoding
> column type in column name.
>
> Thanks,
>
> Eugene
>

I think in the end we will have to create SQL statements using an AST
that can be arbitrarily transformed by the backend.

But this is definitely a future thing.

[Adrian Holovaty]

On 11/11/05, Jakub Labath <jla...@gmail.com> wrote:
> 1. quote_name (easy one)
> MS SQL uses [] to escape words that may be qualifiers e.g. if table
> name is user the statement must look like this "select * from [user]"
> I haven't submitted patch yet but was planning to

Thanks for this info. I've implemented quote_name() for the ado_mssql backend.

[gheorghe]

I did't quite followed the dev of django lately but I see there is this
question about implementing DateTime, Date, Time "casting" from python
to sqlserver.

As some people above said it's imposible to actualy store a time in
sqlserver since the server does not have a time type, what it has is
datetime.

Now if you don't mind ugly hacks (I dont and you saw my implementations
of limit/offset which I see some people find as ugly) then one would be
like this, at least in the sqlserver->python direction:
if year==1899 and month==12 and day==30: return datetime.Time
elif hr==0 and min==0 and sec==0: return datetime.Date
else return datetime.datetime

The Time is ok, the Date is of course probably somewhat problematic if
adding a datetime at midnight. This is the only way I can see this
implemented.
Why 1899/12/30, ask microfost.

Going the other direction there is nothing you need to do because when
you just put a "time string", it will be "1899/12/30 time" datetime, or
if you put a "date string", it will be "date 00:00:00" datetime while
"datetime string" is just that.

For more info on sqlserver in the context of a python ORM somewhat
similar to django, look at my implementation for my own ORM webform
(http://www.emilas.com/pwh/)

I hope this helped

Gheorghe Milas
(http://www.emilas.com/george/)