-2

Good Day!

I am newbie into writing a program,

I need to get the timestampdiff between my 2 column with data type as datetime-local.

below is a sql query that suitable syntax to get the outage duration

SELECT timestampdiff(hour,DateStarted,DateRestored) as Outage from network_outage

however I have no idea how to insert this one in to my database I am using codeigniter framework

see below model code

    function get_networkoutage($OutageDuration)
      {
        $this->db->select('timestampdiff(DateStarted,DateRestored) as OutageDuration',$OutageDuration);
          $query = $this->db->get('network_outage');
          return $query;
      }

below is my Controller code

    $data['NetworkOutage'] = $this->network_model->get_networkoutage($OutageDuration);

after echoing NetworkOutage in view page it gives me an error

A PHP Error was encountered
Severity: Notice

Message: Undefined variable: OutageDuration

Filename: controllers/Site.php

Line Number: 59

Backtrace:

File: D:\xampp\htdocs\itsystem\application\controllers\Site.php
Line: 59
Function: _error_handler

File: D:\xampp\htdocs\itsystem\index.php
Line: 315
Function: require_once

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DateStarted, DateRestored) as OutageDuration FROM `network_outage`' at line 1

SELECT timestampdiff(DateStarted, DateRestored) as OutageDuration FROM `network_outage`

Filename: D:/xampp/htdocs/itsystem/system/database/DB_driver.php

Line Number: 691

Thank you and best regards,

Arjay
  • 23
  • 7
  • 3
    so what have you tried? do you want a PHP or MySQL-based solution? in your previous attempts, what has gone wrong? what errors are you getting? – Javier Larroulet May 14 '19 at 22:02
  • Hi Sir, I never tried anything yet. I am not that sure what is the best practices in doing this, in my case, I want to automatically calculate the time difference when the DateRestored is have value on it here is the procedure, when the problem occurs I am going to put date and time when it was started once it was resolve I will update the form and I wanted that it will be automatically calcucated the TimeDifference and inserted it into the database – Arjay May 14 '19 at 22:56

1 Answers1

0

My answer is a reference to enter link description here, because I can not add it as a comment.

SELECT TIMESTAMPDIFF(SECOND, '2012-06-06 13:13:55', '2012-06-06 15:20:18') will return 7583. As first argument you can use MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Hope this helps

Ivan Ganchev
  • 174
  • 2
  • 10
  • Hi Sir, Thank you for this idea, this answer my problem. I am using codeigniter,I cannot think how to insert automatically this to the database using the codeigniter. thanks in advance – Arjay May 14 '19 at 21:42
  • Most (i'm not sure to every) frameworks provide to insert sql statement as string. If you have to use this solution you have to read about for $this->db->escape(). https://www.codeigniter.com/user_guide/database/results.html . And you can see https://stackoverflow.com/questions/10968527/escaping-sql-queries-in-codeigniter (query with escape example) – Ivan Ganchev May 15 '19 at 04:27
  • Hi Ivan, I am updating my questions the sql syntax you have provided seems fits my needs however, I have no idea on how to implement this one in using codeigniter I wanted to get the value atleast thank you – Arjay May 19 '19 at 21:43
  • Hi Arjay. First check what is the value of $OutageDuration. Second (just for info) for query debug you can see $this->db->last_query(). I think the problem is in $OutageDuration or in way that it is assigned. – Ivan Ganchev May 20 '19 at 16:41
  • Hi Sir Ivan, I really appreciate your time on helping me resolvin this concerns of mine. using the provided sql syntax I come up on creating views with timestampdiff syntax after having the temporay table I fetch them from database and it seems it now help me to resolve my problems. Thank you very much, – Arjay May 23 '19 at 12:47