我最终试图获得两个日期(合同日期和关闭日期)之间的天数差异。它们的日期已经以 0000-00-00 的格式存储在 MySQL 数据库中,但我无法将它们正确插入公式中。我正在对此和其他一些项目使用以下查询。
$q_groups = mysql_query("SELECT CONCAT(contacts.last_name, ', ', contacts.first_name) as Name,
loans.subject_property as SubjectProperty, loans.fKeyUserLoanOfficer,
loan_types.name as LoanType, loans.closed_on as CloseDate, loans.contract_date as ContractDate, loans.amount as Amount, loan_status_types.name as LoanStatus,
loans._id, loans.fKeyReferral
FROM `loans`
LEFT JOIN `loan_status_types` ON (loans.fKeyLoanDetail = loan_status_types._id)
LEFT JOIN `loan_status` ON (loan_status._id = loan_status_types.fKeyType)
LEFT JOIN `loan_types` ON (loans.fKeyLoanType = loan_types._id)
LEFT JOIN `contacts` ON (contacts._id = loans.fKeyContact)
WHERE loans.fKeyGroup = '{$gid}' AND loan_status.name LIKE '%Closed%'
ORDER BY loans.closed_on DESC")
or die(ErrorLog::handle(mysql_error()));
我想做的是让上面查询中的两个日期(合同日期和截止日期)填充下面的日期(红色):
$start = strtotime("2012-08-16");
$end = strtotime("2012-06-28");
$dayz = ($start - $end) / (60 * 60 * 24);
当我查看数据库时,"closed_on"字段的日期为 2012-08-16,当我查看"contract_date"字段时,日期是 2012-06-28...但我无法在没有错误或根本不显示数据的情况下正确插入此信息。
**我遇到的问题是...如何将静态日期替换为字段数据?**
谢谢,艾德
SELECT
TO_DAYS(closed_on) - TO_DAYS(contract_date) AS `dayz`
添加到现有查询:
$q_groups = mysql_query("SELECT CONCAT(contacts.last_name, ', ', contacts.first_name) as Name,
loans.subject_property as SubjectProperty, loans.fKeyUserLoanOfficer,
loan_types.name as LoanType, loans.closed_on as CloseDate, loans.contract_date as ContractDate, loans.amount as Amount, loan_status_types.name as LoanStatus,
loans._id, loans.fKeyReferral,
TO_DAYS(loans.closed_on) - TO_DAYS(loans.contract_date) AS `dayz`
FROM `loans`
LEFT JOIN `loan_status_types` ON (loans.fKeyLoanDetail = loan_status_types._id)
LEFT JOIN `loan_status` ON (loan_status._id = loan_status_types.fKeyType)
LEFT JOIN `loan_types` ON (loans.fKeyLoanType = loan_types._id)
LEFT JOIN `contacts` ON (contacts._id = loans.fKeyContact)
WHERE loans.fKeyGroup = '{$gid}' AND loan_status.name LIKE '%Closed%'
ORDER BY loans.closed_on DESC")
or die(ErrorLog::handle(mysql_error()));
具有正确性的额外好处,因为简单地将时间戳除以一天中的秒数不会跨越 DST 边界等。
如果你坚持使用PHP,你可以使用DateTime对象:
$datetime1 = new DateTime('2009-10-11');
$datetime2 = new DateTime('2009-10-13');
$interval = $datetime1->diff($datetime2);
echo $interval->format('%R%a days');
日期时间::d iff() 手册。
如果你的数据库中已经有数据,我建议使用它,因为它比staticsan指出的更快。
一个示例是这样的,但是您将用表中的字段数据替换下面的静态日期:
SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
我建议你让MySQL进行数据操作。它具有比PHP更好的函数范围,以及本机日期/时间数据类型。您可以使用明确的语言添加和减去时间和日期,并且您没有往返Unix时间戳的问题。
而且你不想用Unix时间戳来做这件事!这是因为对于日期操作,它提供了太多的精度:秒与日,这是一个好兆头,它是错误的解决方案。他们也不允许时区和夏令时过渡之类的事情,试图自己处理这个问题是一个漫长的滑坡的开始。使用适当的日期处理函数要好得多,因为它们已经过广泛的调试和正常工作。
你也可以使用新的PHP DateTime库,但是如果你的日期来自MySQL并回到MySQL,只需使用MySQL;它的转换较少。