将0000-00-00 00:00:00 datetime从mysql插入postgresql

Question

I'm trying to go from a mysql database to a postgresql database. I created a script with this query

 if ($result->num_rows > 0) {
     while($row = $result->fetch_assoc()) { 

     $psql = "INSERT INTO subscriptions_sub (sub_id,cust_id,tarrif_id,des,datetime_created,datetime_modified,credit,credit_limit,status,date_start,date_end,backoffice_userref,backoffice_deleted,backoffice_createdon,backoffice_createdby, backoffice_updatedon,backoffice_updatedby,backoffice_administration,backoffice_readonly,backoffice_order,backoffice_dummy)
    VALUES('" .(int) $row["sub_id"] . "','" . (int) $row["cust_id"] . "','" . (int) $row["tarrif_id"] . "','" . $row["des"] . "','" . $row["datetime_created"] . "','" . $row["datetime_modified"] . "','" . $row["credit"] . "','" . $row["credit_limit"] . "','" . $row["status"] . "','" . $row["date_start"] . "','" . $row["date_end"] . "','" . $row["backoffice_userref"] . "','" . $row["backoffice_deleted"] . "','" . $row["backoffice_createdon"] . "','" . $row["backoffice_createdby"] . "','" . $row["backoffice_updatedon"] . "','" . (int) $row["backoffice_updatedby"] . "','" . (int) $row["backoffice_administration"] . "','" . $row["backoffice_readonly"] . "','" . (int)$row["backoffice_order"] . "','" . $row["backoffice_dummy"] . "');
    ";
    print_r($row);

    $ret = pg_query($db, $psql);
    if(!$ret){
        echo pg_last_error($db);
    } else {
        echo "Records created succesfully
";
    }

}

this is my subscription.sub

 _columns = {
    'sub_id': fields.integer('Subscription ID', size=10),
    'cust_id': fields.many2one('res.partner','customer_id', 'Customer ID'),
    'tarrif_id': fields.integer('Tariefplan ID', size=10, required=True),
    'des': fields.char('Description', size=64),
    'datetime_created': fields.datetime('Created', default='0'),
    'datetime_modified': fields.datetime('Modified', default='0'),
    'credit': fields.float('Credit', digits=(10,4)),
    'credit_limit': fields.float('Credit limit', digits=(10,4)),
    'status': fields.selection([('active','Active'),('inactive','Inactive'),('ended','Ended'),('deleted','Deleted'),('disabled','Disabled')], 'Status', default='active', required=True),
    'date_start': fields.date('Date start'),
    'date_end': fields.date('Date end'),
    'backoffice_userref': fields.integer('Backoffice_userref', size=10),
    'backoffice_deleted': fields.boolean('Backoffice_deleted', size=1, default='0', required=True),
    'backoffice_createdon': fields.datetime('Backoffice_createdon', default='0'),
    'backoffice_createdby': fields.integer('Backoffice_createdby', size=10, default='0', required=True),
    'backoffice_updatedon': fields.datetime('Backoffice_updatedon', default='0'),
    'backoffice_updatedby': fields.integer('Backoffice_updatedby', size=10, default='0', required=True),
    'backoffice_administration': fields.integer('Backoffice_administration', size=10, default='1', required=True),
    'backoffice_readonly': fields.boolean('Backoffice_readonly', size=1, default='0', required=True),
    'backoffice_order': fields.integer('Backoffice_order', size=20, default='-1', required=True),
    'backoffice_dummy': fields.boolean('Backoffice_dummy', size=1, default='0', required=True),
    #'qty_available': fields.related('customer_id','subscription_id',type='char', relation="partner.res", string="subscription", store=True)
}

for example: the datetime_created field has the value '0000-00-00 00:00:00' in the mysql database with type TIMESTAMP.
When I execute my script is shows the error:

ERROR: date/time field value out of range: "0000-00-00 00:00:00".

Is it possible to convert the datetime from mysql to postgresql?

Answer 1

create table mytbl1 (dte timestamp);

insert into mytbl1 values (now());
--Query returned successfully: one row affected

insert into mytbl1 values ('0000-00-00 00:00:00');
--ERROR:  date/time field value out of range: "0000-00-00 00:00:00"

Because PostgreSQL doesn't accept 0 or 0000-00-00 00:00:00,So you can use NULLIF() function like below

insert into mytbl1 
values(NULLIF('0000-00-00 00:00:00','0000-00-00 00:00:00')::timestamp);

so you should arrange insert statement according to this

insert into subscriptions_sub(datetime_created) 
values (NULLIF(your_field_name,'0000-00-00 00:00:00')::timestamp)