如何在postgresql和Codeigniter中使用转换格式日期来JOIN表

Question

I'am new on postgresql, i have some query like this :

SELECT * FROM "trx_mutualfund_trade" RIGHT JOIN "trx_mutualfund_nav" ON "trx_mutualfund_trade"."mtr_trade_time"::timestamp::date = trx_mutualfund_nav.mna_efective_date ....

and i try to write CI code like this :

$this->db->select('*');
$this->db->from('mutualfund_trade');
$this->db->join('mutualfund_nav', 'mutualfund_trade.mtr_trade_time::timestamp::date = mutualfund_nav.mna_efective_date', 'right');

but that CI Code produces query like this :

SELECT * FROM "trx_mutualfund_trade" RIGHT JOIN "trx_mutualfund_nav" ON "trx_mutualfund_trade"."mtr_trade_time"::"timestamp::date" = mutualfund_nav.mna_efective_date...

So that can't run because there is an doublequote on ::"timestamp::date"

Anyone can help me to show me how to write the CI code corectly? thanks

Answer 1

Use this:

$query = $this->db->query("SELECT * FROM trx_mutualfund_trade RIGHT JOIN trx_mutualfund_nav ON trx_mutualfund_trade.mtr_trade_time::timestamp::date = trx_mutualfund_nav.mna_efective_date");
$result = $query->result_array();
return $result