How to insert current datetime to oracle database in codeigniter

Question

I am trying to insert current datetime to oracle database in codeigniter but getting error as follows:

ORA-01858: a non-numeric character was found where a numeric was expected

INSERT INTO "USER_LOGS" ("USER_ID", "IP_ADDRESS", "LOGIN_DATE", "LOG_TYPE", "USERNAME") VALUES ('1', '::1', 'TO_DATE(''2018-07-17 03:33:03'',''yyyy/mm/dd hh24:mi:ss'')', 'LOG OUT', 'admin')

My Code:

date_default_timezone_set("Asia/Dhaka");
$timestamp = date('Y-m-d h:i:s');
$timestamp = "TO_DATE('$timestamp','yyyy/mm/dd hh24:mi:ss')";
$user_log_data = array(
   'USER_ID' => $ses_data['USER_ID'],
   'IP_ADDRESS' => $this->input->ip_address(),
   'LOGIN_DATE' => $timestamp,
   'LOG_TYPE' => 'LOG OUT',
   'USERNAME' => $ses_data['USERNAME']
);
$this->utilities->insertData($user_log_data, 'USER_LOGS');

My Insert Statement:

function insertData($post, $tableName)
    {
        $this->db->trans_start();
        $this->db->insert($tableName, $post);
        $this->db->trans_complete();
        if ($this->db->trans_status() == TRUE) {
            return TRUE;
        } else {
           return FALSE;
        }


    }

For your kind information, the data type of the field (LOGIN_DATE) is TIMESTAMP

Thanks

Answer 1

My datatype column table is TIMESTAMP then I use like this in my controller, and it works:

public function __construct() {
 parent::__construct();    
 
public function update() {

$date = new DateTime();
$date->setTimezone(new DateTimeZone('Asia/Jakarta'));

$data = array( 
'UPDATED_AT' => $date->format('d-M-y h:i:s A'),
 );
 $this->kelola->update_piping(array('JOINT_NO' => $joint , 'DWG_PIPE_ID' 
  => $dwgPipeId), $data, $data1);
        
   echo json_encode(array("status" => TRUE));
    }

Answer 2

Finally, I have done it by using CI date helper and making a custom Oracle date format method in system/core/Controller.php or you can create your own controller inheriting CI_Controller like

//system/core/Controller.php
class CI_Controller {
function __construct() {
        parent::__construct();            
    }

public function oracle_date($timestamp='')
    {
        $this->load->helper('date');
        if($timestamp=='date'){
            $datestring = '%d-%M-%Y';
        }
        else
        {
            $datestring = '%d-%M-%Y %h.%i.%s %a';
        }

        $time = time();
        $timestamp = strtoupper(mdate($datestring, $time));
        return $timestamp;
    }

 }

//OR

//application/core
class MY_Controller extends CI_Controller {
function __construct() {
        parent::__construct();            
    }

public function oracle_date($timestamp='')
    {
        $this->load->helper('date');
        if($timestamp=='date'){
            $datestring = '%d-%M-%Y';
        }
        else
        {
            $datestring = '%d-%M-%Y %h.%i.%s %a';
        }

        $time = time();
        $timestamp = strtoupper(mdate($datestring, $time));
        return $timestamp;
    }

 }

//Then use as follows:

//application/controllers
class User extends MY_Controller {
    public function __construct(){
          parent::__construct();
    }

   $timestamp = $this->oracle_date('timestamp'); //if parameter is 'date' it will return only date like DD-MM-YY and 'timestamp' it will return full timestamp

}

//OR

//application/controllers
class User extends CI_Controller {
    public function __construct(){
          parent::__construct();
    }

   $timestamp = $this->oracle_date('timestamp'); //if parameter is 'date' it will return only date like DD-MM-YY and 'timestamp' it will return full timestamp


}

If you create a custom controller you can also change your prefix MY_ in the config.php file like $config['subclass_prefix'] = 'MY_'; to $config['subclass_prefix'] = 'Your_';

Answer 3

You may build your INSERT statement differently like this:

$sql = 
   "INSERT INTO USER_LOGS (USER_ID, IP_ADDRESS, LOGIN_DATE, LOG_TYPE, USERNAME) 
    VALUES (?, ?, TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'), ?, ?)";

$timestamp = date('Y-m-d h:i:s');
$user_log_data = array(
   $ses_data['USER_ID'],
   $this->input->ip_address(),
   $timestamp,
   'LOG OUT',
   $ses_data['USERNAME']
);

$this->db->query($sql, user_log_data);

Or if you ask for "current datetime", then simply:

$sql = 
   "INSERT INTO USER_LOGS (USER_ID, IP_ADDRESS, LOGIN_DATE, LOG_TYPE, USERNAME) 
    VALUES (?, ?, SYSDATE, ?, ?)";
$user_log_data = array(
   $ses_data['USER_ID'],
   $this->input->ip_address(),
   'LOG OUT',
   $ses_data['USERNAME']
);
$this->db->query($sql, user_log_data);

However, I don't know whether db->query() works also for DML statements.

Answer 4

Please try after remove single quotes from $timestamp variable.

    date_default_timezone_set("Asia/Dhaka");
    $timestamp = date('Y-m-d h:i:s');
    $timestamp = "TO_DATE($timestamp,'yyyy/mm/dd hh24:mi:ss')";
    $user_log_data = array(
       'USER_ID' => $ses_data['USER_ID'],
       'IP_ADDRESS' => $this->input->ip_address(),
       'LOGIN_DATE' => $timestamp,
       'LOG_TYPE' => 'LOG OUT',
       'USERNAME' => $ses_data['USERNAME']
    );
    $this->utilities->insertData($user_log_data, 'USER_LOGS');