EXTRACT() not working as expected in PostgreSQL with TIMESTAMP WITH TIMEZONE

[Martijn van Oosterhout]

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.

I think it's a bug and if people agree I can file one. But is there a workaround to suppress the cast?

Thanks in advance,
--
Martijn van Oosterhout <kle...@gmail.com>

[Martijn van Oosterhout]

For the record and for other people running into the same problem, here's a workaround that kills the cast by wrapping the column in a function that does nothing:

session.query(extract('epoch', func.timestamptz(database.TestTable.ts)))

This of course will only work until sqlalchemy learns about all the builtin casts, but for now it's fine.

Have a nice day,

On Tuesday, June 4, 2013 10:53:22 AM UTC+2, Martijn van Oosterhout wrote:

<snip>

[Michael Bayer]

On Jun 4, 2013, at 4:53 AM, Martijn van Oosterhout <kle...@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.