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.
