gistfile1.txt

Postgresql

Schema :

typo_tests=# \d contents              
                                      Table « public.contents »
    Colonne     |            Type             |                     Modificateurs
----------------+-----------------------------+-------------------------------------------------------
 id             | integer                     | not null default nextval('contents_id_seq'::regclass)
 type           | character varying(255)      |
 title          | character varying(255)      |
 author         | character varying(255)      |
 body           | text                        |
 extended       | text                        |
 excerpt        | text                        |
 keywords       | character varying(255)      |
 created_at     | timestamp without time zone |
 updated_at     | timestamp without time zone |
 user_id        | integer                     |
 permalink      | character varying(255)      |
 guid           | character varying(255)      |
 text_filter_id | integer                     |
 whiteboard     | text                        |
 name           | character varying(255)      |
 published      | boolean                     | default false
 allow_pings    | boolean                     |
 allow_comments | boolean                     |
 published_at   | timestamp without time zone |
 state          | character varying(255)      |
Index :
    « contents_pkey » PRIMARY KEY, btree (id)
    « index_contents_on_published » btree (published)
    « index_contents_on_text_filter_id » btree (text_filter_id)


WITH POSTGRESQL 8.2 :
typo_tests=# select * from contents WHERE published_at LIKE '%2004-06%' LIMIT 1;
    id     |  type   | title  |     author     | body |     extended     | excerpt | keywords |     created_at      |     updated_at      |  user_id  | permalink |                 guid                 | text_filter_id | whiteboard | name | published | allow_pings | allow_comments |    published_at     |   state
-----------+---------+--------+----------------+------+------------------+---------+----------+---------------------+---------------------+-----------+-----------+--------------------------------------+----------------+------------+------+-----------+-------------+----------------+---------------------+-----------
 411617044 | Article | ルビー | user_publisher | body | extended content |         |          | 2004-06-02 20:00:03 | 2004-06-02 20:00:03 | 284627120 | ルビー    | a87c4220-18d4-11db-aadd-1002a5d5c51d |                |            |      | t         | t           | t              | 2004-06-02 20:00:03 | published
(1 ligne)

WORKS

With PosgreSQL 8.3 :
typo_tests=> select published_at from contents WHERE published_at LIKE '%2004-06%' LIMIT 1;
ERREUR:  l'opérateur n'existe pas : timestamp without time zone ~~ unknown
LINE 1: ...ect published_at from contents WHERE published_at LIKE '%200...
                                                             ^
HINT:  Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.

FAILED :(

typo=> select published_at from contents WHERE published_at BETWEEN '01-07-08' AND '31-07-08';
    published_at
---------------------
 2008-07-22 18:00:00
(1 ligne)

WORKS