Pgloader: Mysql to PostgreSQL : Convert to timestamp without timezone

[rap2hpoutre]

I have a timestamp in MySQL 0000-00-00 00:00:00 and i want to convert it to a timestamp without timezone (I dont want to break compatibility). Is there any way to do this? I tired to cast like type timestamp to timestamp but it does not work, my timestamp are timezoned...Timestamp in mysql are timestamp NULL DEFAULT NULL

[dimitri]

I guess the MySQL source type for the cast is spelled datetime, right?

[rap2hpoutre]

I tried to cast type timestamp to timestamp and type datetime to timestamp, it generates a timezoned timestamp in both case. Manually exporting from MySQL gives me timestamp NULL DEFAULT NULL

[dimitri]

I just tried with the following cast rule added in the test/sakila.load test file and got what I wanted:

type datetime to timestamp drop default drop not null using zero-dates-to-null

\d sakila.payment
                                              Table "sakila.payment"
    Column    |            Type             |                              Modifiers                              
--------------+-----------------------------+---------------------------------------------------------------------
 payment_id   | integer                     | not null default nextval('sakila.payment_payment_id_seq'::regclass)
 customer_id  | smallint                    | not null
 staff_id     | smallint                    | not null
 rental_id    | bigint                      | 
 amount       | numeric(5,2)                | not null
 payment_date | timestamp without time zone | 
 last_update  | timestamp with time zone    | not null default now()

Note that the last_update column is of type datetime in the source database.

[dimitri]

So please refresh your code from the latest git version and try the --debug option where you should see the CAST decisions in the logs.

[dimitri]

Any news?