datetime and engine

[jo]

Hi all,

I found a difference between Oracle and PostgreSQL about datetime objects.
Using the engine Oracle returns every date column as datetime.datetime
while PostgreSQL returns it as datetime.date

I have a table with a column data_inizio defined as DATE in table
tariffa in my db.

take a look:

In [5]: engine.connect().execute('select data_inizio from tariffa where
id=391').fetchone()
Out[5]: (datetime.datetime(2009, 1, 1, 0, 0),)

In [6]: type(Tariffa.get(391).tariffa_data_inizio)
Out[6]: <type 'datetime.date'>

----------------

In [1]: engine.connect().execute('select data_inizio from tariffa where
id=391').fetchone()
Out[1]: (datetime.date(2009, 1, 1),)

In [2]: type(Tariffa.get(391).tariffa_data_inizio)
Out[2]: <type 'datetime.date'>

Why this difference? Is there a way to change Oracle behavior in engine?

thanks for any help.

j

[shinriyo]

hi jo

Oracle and PostgreSQL are different.
Oracle also have hour and minutes and second.

If you want minutes and second on PostgresQL, you should use datetime.

[jo]

Hi shinryo,

I don't want hours and minutes.
My problem is that I have a comparison in my code like this:

if data_inizio > data_fine:
...

TypeError: can't compare datetime.datetime to datetime.date

--------------

data_inizio is a value returned by:
Frazione.get(31).tariffa_data_inizio # returns an object
datetime.date (both oracle and pg)

data_fine is a value returned by engine: #oracle returns a datetime
while pg returns a date
engine.connect().execute('select data_fine from tariffa where
id_frazione=31').fetchone()

I would like to know why Oracle engine returns dates as
datetime.datetime instead of datetime.date
and if there is a way to have the same behavior between oracle and pg in
such case.

j

[Mike Conley]

The Oracle database only has one date type, and it includes time. If you require date only it is up to you to extract the date. Alternatively, if you really care about date only for all usage of that column, store it with a time of 00:00:00.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[jo]

Mike Conley wrote:
>
> The Oracle database only has one date type, and it includes time. If
> you require date only it is up to you to extract the date.
> Alternatively, if you really care about date only for all usage of
> that column, store it with a time of 00:00:00.
>

the value stored in such column is already with time 00:00:00
my problem is when I try to campare it with a date:

data_inizio = datetime.datetime(2012, 3, 31, 0, 0, 0, 0)
data_fine = datetime.date(2012, 3, 31)

if data_inizio > data_fine:
...

TypeError: can't compare datetime.datetime to datetime.date

With DB PostgreSQL it works, because Pg returns a date object,
but when I change DB to Oracle it doesn't work anymore.

I know that I can transform it to a date as:
data_inizio.date()

but when I use PostgeSQL it is already a date and I can't use this syntax:

AttributeError: 'datetime.date' object has no attribute 'date'

I would not change my code to test if I'm using Oracle or Pg if there's
another way...

j

> On Mar 30, 2012 8:08 AM, "jo" <jose....@sferacarta.com

> <mailto:sqlal...@googlegroups.com>.

> To unsubscribe from this group, send email to

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to

> sqlalchemy+...@googlegroups.com.

> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.

--
Jose Soares _/_/
Sferacarta Net
Via Bazzanese 69 _/_/ _/_/_/
40033 Casalecchio di Reno _/_/ _/_/ _/_/
Bologna - Italy _/_/ _/_/ _/_/
Ph +39051591054 _/_/ _/_/ _/_/ _/_/
fax +390516131537 _/_/ _/_/ _/_/ _/_/
web:www.sferacarta.com _/_/_/ _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l�integrit� e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

[Michael Bayer]

use a TypeDecorator that truncates the time portion off of the date as returned by Oracle.

http://docs.sqlalchemy.org/en/latest/core/types.html#augmenting-existing-types

> Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

>
> This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.
>