TIMESTAMPDIFF not working on mysql query in codeigniter

Question

So basically I have a TIMESTAMPDIFF query in my model to determine the duration and I want it to display the duration in a view. The problem is, it will display the error.

Please help me, thank you.

Here's my query of my model (model_attendance.php):

public function getOne($username){
        $sql = "SELECT empnum FROM employees WHERE username='$username'";

        $result = $this->db->query($sql);

        $sql1 = "SELECT a.empnum,CONCAT(a.name,' ',a.midname,' ',a.lastname) AS
                NAME,CONCAT(b.indate,' ',b.intime) AS 'TIMEIN',CONCAT(b.outdate,'
                ',b.outtime)AS 'TIMEOUT', TIMESTAMPDIFF(HOUR, 'TIMEIN','TIMEOUT') AS 'DUR' 
                FROM employees AS a
                JOIN times AS b ON (a.empnum=b.userid)
                WHERE b.indate BETWEEN
                DATE(CONCAT(YEAR(CURRENT_TIMESTAMP),'-',MONTH(CURRENT_TIMESTAMP),'-','01'))
                AND DATE(LAST_DAY(CURRENT_TIMESTAMP)) AND b.userid='".$result->row(0)->empnum."'";

        $query = $this->db->query($sql1);

        return $query->result();
    }

Lalit Giriya · Accepted Answer · 2018-01-05 06:50:18Z

1

To solve this problem, you need to Pass return type as "false" to Active record select query.

$this->db->select("TIMESTAMPDIFF(YEAR,a.date_birth, CURDATE()) AS age", false);

answered Jan 5, 2018 at 6:50

Lalit Giriya

3121 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abdulla Nilam · Accepted Answer · 2016-01-29 23:01:49Z

0

Assign that to variable and pass that to your SQL Query.

$now = date('Y-m-d H:i:s'); # 2015-1-30 04:32:25 / With time
$now = date('Y-m-d'); # 2015-1-30 / Without time

answered Jan 29, 2016 at 23:01

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

Comments

jsdev · Accepted Answer · 2016-02-11 01:56:54Z

0

Its potentially a bug. I Havent checked the Query Builder class yet. But to fix this...you can try by adding ` to the column like this

$this->db->select("TIMESTAMPDIFF(YEAR, `a.date_birth`, CURDATE()) AS age");

or

$this->db->query("TIMESTAMPDIFF(YEAR, `a.date_birth`, CURDATE()) AS age");

answered Feb 11, 2016 at 1:56

jsdev

6706 silver badges16 bronze badges

Collectives™ on Stack Overflow

TIMESTAMPDIFF not working on mysql query in codeigniter

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related