在PHP / Oracle中使用日期范围 [英] Working with date ranges in PHP/Oracle

问题描述

我需要弄清楚在不同的日子里发生了多少个不同的实例，从许多不同的范围。可能最好以一个例子解释。

I need to work out how many different instances occur on a different day, from many different ranges. Probably best to explain it with an example.

18-JAN-09 to 21-JAN-09
19-JAN09 to 20-JAN-09
20-JAN-09 to 20-JAN-09

使用上面的三个例子，我需要它来收集这些信息并显示一些类似...

Using the three examples above, I need it to collect this information and display something a little like...

18th Jan: 1
19th Jan: 2
20th Jan: 3
21st Jan: 1

...我将从Oracle数据库fwiw（因此上面的格式）获取信息，并且将有数百个，也就是数千个记录，所以我的跛脚尝试做各种循环和if语句将永远运行。

... I'll be grabbing the information from an Oracle database fwiw (hence the format above ^) and there will be hundreds, maybe thousands of records, so my lame attempt to do all sorts of loops and if statements would take forever to run.

有没有一个相当简单而有效的方法呢？我真的不太确定哪里可以开始...

Is there any fairly simple and efficient way of doing this? I'm really not too sure where to start unfortunately...

谢谢

推荐答案

您的DB引擎或您的PHP代码将不得不在日期范围内循环。

Either your DB engine or your PHP code is going to have to loop over the date range.

以下是一些PHP代码来进行求和。日期计数由年月存储，以避免在广泛的日期范围内拥有一个庞大的数组。

Here's some PHP code to do the summation. The day counts are stored by year-month to avoid having a huge array for a wide date range.

<?php

// Get the date ranges from the database, hardcoded for example
$dateRanges[0][0] = mktime(0, 0, 0, 1, 18, 2009);
$dateRanges[0][1] = mktime(0, 0, 0, 1, 21, 2009);
$dateRanges[1][0] = mktime(0, 0, 0, 1, 19, 2009);
$dateRanges[1][1] = mktime(0, 0, 0, 1, 20, 2009);
$dateRanges[2][0] = mktime(0, 0, 0, 1, 20, 2009);
$dateRanges[2][1] = mktime(0, 0, 0, 1, 20, 2009);

for ($rangeIndex = 0; $rangeIndex < sizeof($dateRanges); $rangeIndex++)
{
  $startDate = $dateRanges[$rangeIndex][0];
  $endDate = $dateRanges[$rangeIndex][1];

  // Add 60 x 60 x 24 = 86400 seconds for each day
  for ($thisDate = $startDate; $thisDate <= $endDate; $thisDate += 86400)
  {
    $yearMonth = date("Y-m", $thisDate);
    $day = date("d", $thisDate);

    // Store the count by year-month, then by day
    $months[$yearMonth][$day]++;
  }
}

foreach ($months as $yearMonth => $dayCounts)
{
  foreach ($dayCounts as $dayNumber => $dayCount)
  {
    echo $yearMonth . "-" . $dayNumber . ": " . $dayCount . "<br>";
  }
}

?>

这篇关于在PHP / Oracle中使用日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！