This project is archived and is in readonly mode.

#49 ✓invalid
Psycopg website

to_timestamp not working in 2.4

Reported by Psycopg website | April 3rd, 2011 @ 04:44 AM

Submitted by: Mark Lakewood

Hi all.

Im running a raw sql query through django 1.2.5 that uses the to_timestamp() postgres function. in psycopg2 2.0.x it works. when I use 2.4 it puts out the following traceback

http://pgsql.privatepaste.com/3006a9e813

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo April 3rd, 2011 @ 11:06 PM

    • State changed from “new” to “invalid”

    PostgreSQL is now (since before 2.4) more strict about types. You are passing a Python date to psycopg, so psycopg is passing a date to postgres. There is no function to_timestamp(date, whatever).

    From the traceback you have sent it's hard to understand the context where the date is used. If it's just used for the comparison in the WHERE you don't need to parse the timestamp on the server: the call to_timestamp() can be dropped at all from your code. If you have to do some timestamp arithmetic in the query and the value is just a date, then use a cast such as %s::timestamp.

    This is not a bug. If you need help please write to the mailing list.

  • Mark Lakewood

    Mark Lakewood April 4th, 2011 @ 01:28 AM

    Hi Daniele,

    Sorry, I should've put the SQL in to make it easier

    SELECT COUNT(timestamp), album_artist, track_name, music_playlist.name

            FROM music_song_played 
            JOIN music_song ON music_song_played.song_played_id = music_song.id 
            JOIN music_user_listened ON music_song_played.playlist_from_id = music_user_listened.playlist_id 
            JOIN auth_user ON music_user_listened.user_id = auth_user.id 
            JOIN music_playlist ON music_user_listened.playlist_id = music_playlist.id 
            WHERE (music_song_played.timestamp > to_timestamp(%s, 'YYYY-MM-DD') AND 
                   music_song_played.timestamp < to_timestamp(%s, 'YYYY-MM-DD') ) AND 
                   music_song_played.timestamp > music_user_listened.start  AND 
                  music_song_played.timestamp < music_user_listened.end 
            GROUP BY timestamp, album_artist, track_name, music_playlist.name;"
    

    what im trying to do is cast a Date to a timestamp so it can be compared in the where clause to a timestamp. The reason I think this is a bug is that the SQL works in postgres 8.4. ie if I plug this into it 8.4 it will work. I understand if postgreSQL has become more strict about types. But If I can execute this sql against my postgres database, surely something is wrong here?

    I'll have a look at the %s::timestamp to see if that fixes my problem.

    Cheers

    Mark

  • Mark Lakewood

    Mark Lakewood April 5th, 2011 @ 01:49 AM

    Hi Daniele,

    The %s::timestamp does work, so im fine.

    I guess its just a bit strange for postgresql to accept the sql but for psycopg2 to not accept it. Might be something that needs to be looked into?

    Cheers

    Mark

  • Daniele Varrazzo

    Daniele Varrazzo April 5th, 2011 @ 07:46 AM

    No the behavior is perfectly known and we consider it correct. When you pass a date object to psycopg it creates a postgres date, not an unknown literal, by adding a cast. This behavior changed somewhere around psycopg 2.2, and has broken your program because it was doing something not correct: it is passing a python date to a postgres function taking a string argument.

    >>> cur.execute("select %s as mydate", (date(2011,04,05),))
    
    >>> cur.query  # psycopg has added a cast
    "select '2011-04-05'::date as mydate"
    
    >>> cur.fetchone()  # the type is "strong" and we have been able
                        # to make a complete roundtrip with it
    (datetime.date(2011, 4, 5),)
    

    The cast we added helps postgres to disambiguate the type when it's used in an ambiguous context such as argument of an overloaded function, which is exactly the reason your original program may have needed the parse_date kludge. I believe it was needed somewhere else, not in the query you sent, where an "unknown" type would have worked as well.

    Also note that I'm pretty sure you don't need a cast at all for the query you have written above: the cast from date to timestamp is implicit, which means Postgres will do it by itself. The cast may have been used instead of parse_date with older psycopg versions too (where the ::date wasn't appended) and it would have been a better solution, as the query parser would have known the values at planning time, whereas the function call hides the value and may force the selection of a suboptimal plan.

    Using to_date to parse psycopg arguments is just wrong: it is unneeded in your example query, it uses the wrong argument type and, in case somewhere else a timestamp is really needed, it's better done with a cast.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins

People watching this ticket

Pages