On Jun 4, 2013, at 4:53 AM, Martijn van Oosterhout <klep...@gmail.com> wrote:
> I have just upgraded sqlalchemy and am running some tests on some old code
> and getting some very strange results.
>
> I have a table with a column defined as TIMESTAMP WITH TIMEZONE:
>
> test_table = Table('test', metadata,
> Column('id', Integer, primary_key=True),
> Column('data', UnicodeText, primary_key=True),
> Column('ts', DateTime(timezone=True), nullable=False),
> )
>
> And I then have a query which uses it like so (TestTable is the ORM mapping
> of test_table):
>
> session.query(extract('epoch', database.TestTable.ts))
>
> and this is producing output like:
>
> SELECT EXTRACT(epoch FROM test_table.ts :: timestamp);
>
> That cast is a problem, since casting a timestamptz to a timestamp removes
> the timestamp info, which means you get a different answer, since it's now
> interpreted as UTC, which means the answer is (in my case) two hours off:
>
> # select extract(epoch from '2013-06-02 11:23:45+02'::timestamptz);
> date_part
> ------------
> 1370165025
> (1 row)
>
> # select extract(epoch from '2013-06-02 11:23:45+02'::timestamptz::timestamp);
> date_part
> ------------
> 1370172225
> (1 row)
>
> This cast seems to have been introduced in response to
> http://www.sqlalchemy.org/trac/ticket/1647 which seems to completely ignore
> the WITH TIMEZONE case. I tested it on 0.5.4, but on 0.7.6 it's definitely
> broken. What I don't understand is why the cast is there at all. Unless the
> argument is text postgresql can work out the type by itself just fine.
Note that postgresql is not the only actor needed here, the DBAPI needs to
interpret a literal Python interval type into an appropriate string format,
which modern versions of psycopg2 can do just fine.
The cast here is much older than that, that ticket only refers to some
adjustments to not render the cast in some cases. It was there when the
EXTRACT feature was first added, as you can see here:
http://www.sqlalchemy.org/trac/changeset/aca84bebb091a51ceeb#file4
So unfortunately I don't have a clear picture of the rationale for this cast,
the best guess would be that old versions of psycopg2 around 2009 were unaware
of the Python interval type. I just tested with 2010's 2.2.2 and that one
seems to know about intervals.
This certainly should be removed (http://www.sqlalchemy.org/trac/ticket/2740)
but I believe it has to be in 0.9 only as 0.8 is in maintenance mode.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
