php - 根据带有 unix 时间戳的月份和年份选择总和

Question

我正在尝试对 10 月份的列中的所有值求和，但我似乎有 unix 时间戳

我尝试了以下但值仍然为 0

<?php 
echo $this->db->select('(SELECT SUM(amount_paid) FROM invoice WHERE MONTH (  `creation_timestamp` ) = 10) AS invoice');

$query = $this->db->get('invoice'); 
?>

..

架构

CREATE TABLE `invoice`(
    `invoice_id` int(11) NOT NULL AUTO_INCREMENT,
    `student_id` int(11) NOT NULL,
    `title` longtext COLLATE utf8_unicode_ci NOT NULL,
    `description` longtext COLLATE utf8_unicode_ci NOT NULL,
    `amount` int(11) NOT NULL,
    `amount_paid` longtext COLLATE utf8_unicode_ci NOT NULL,
    `due` longtext COLLATE utf8_unicode_ci NOT NULL,
    `creation_timestamp` int(11) NOT NULL,
    `payment_timestamp` longtext COLLATE utf8_unicode_ci NOT NULL,
     `payment_method` longtext COLLATE utf8_unicode_ci NOT NULL,
    `payment_details` longtext COLLATE utf8_unicode_ci NOT NULL,
    `status` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT 'paid or unpaid',
    PRIMARY KEY (`invoice_id`)
) ENGINE=MyISAM AUTO_INCREMENT=73 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

试一试，因为我们正在寻找 2015 年 10 月的总和(我认为)

SELECT UNIX_TIMESTAMP('2015-10-01'); -- '1443672000'
SELECT UNIX_TIMESTAMP('2015-10-31 23:59:59'); -- 1446350399
SELECT UNIX_TIMESTAMP('2015-11-01'); -- '1446350400'

注意上面的值

drop table invoice2;
create table invoice2
(   id int auto_increment primary key,
    amount decimal(12,2) not null,
    ts int(11) not null -- from unix timestamp
);

-- truncate table invoice2;
insert invoice2(amount,ts) values 
(10,unix_timestamp('2015-10-01')),
(10,unix_timestamp('2015-10-01')),
(3310,unix_timestamp('2015-11-01')),
(3310,unix_timestamp('2015-11-01')),
(44410,unix_timestamp('2016-01-01')),
(5510,unix_timestamp('2016-02-01')),
(6610,unix_timestamp('2016-02-01'));

所以您想要 2015 年 10 月金额的以下 flavor 的东西:

select sum(amount) as theSum 
from invoice2 
where month(from_unixtime(ts))=10  
and year(from_unixtime(ts))=2015;
+--------+
| theSum |
+--------+
|  20.00 |
+--------+

---

感兴趣的函数是get_sum()

代码点火器模型

 <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
 class department_model extends CI_Model{

 function __construct()
 {
      // Call the Model constructor
      parent::__construct();
 }

 //read the department list from db
 function get_department_list()
 {
      $sql = 'select var_dept_name, var_emp_name from tbl_dept, tbl_emp where tbl_dept.int_hod = tbl_emp.int_id';
      $query = $this->db->query($sql);
      $result = $query->result();
      return $result;
 }

 // get the sum of amount based on parameters month and year passed
 function get_sum($month,$year) 
 {    $myQuery="select sum(amount) as theSum from invoice2 where month(from_unixtime(ts))=$month  
                and year(from_unixtime(ts))=$year";
      $query = $this->db->query($myQuery);

      $result = $query->result();
      return $result;
 }