Day difference without weekends

Question 1

I want to count the total day difference from user input

For example when the user inputs

start_date = 2012-09-06 and end-date = 2012-09-11

For now I am using this code to find the diffeence

$count = abs(strtotime($start_date) - strtotime($end_date));
$day   = $count+86400;
$total = floor($day/(60*60*24));

The result of total will be 6. But the problem is that I dont want to include the days at weekend (Saturday and Sunday)

2012-09-06
2012-09-07
2012-09-08 Saturday
2012-09-09 Sunday
2012-09-10
2012-09-11

So the result will be 4

----update---

I have a table that contains date,the table name is holiday date

for example the table contains 2012-09-07

So, the total day will be 3, because it didn't count the holiday date

how do I do that to equate the date from input to date in table?

Question 2

Very easy with my favourites: DateTime, DateInterval and DatePeriod

$start = new DateTime('2012-09-06');
$end = new DateTime('2012-09-11');
// otherwise the  end date is excluded (bug?)
$end->modify('+1 day');

$interval = $end->diff($start);

// total days
$days = $interval->days;

// create an iterateable period of date (P1D equates to 1 day)
$period = new DatePeriod($start, new DateInterval('P1D'), $end);

// best stored as array, so you can add more than one
$holidays = array('2012-09-07');

foreach($period as $dt) {
    $curr = $dt->format('D');

    // substract if Saturday or Sunday
    if ($curr == 'Sat' || $curr == 'Sun') {
        $days--;
    }

    // (optional) for the updated question
    elseif (in_array($dt->format('Y-m-d'), $holidays)) {
        $days--;
    }
}


echo $days; // 4

Question 3

The problem is that your code generates a faulty SQL syntax, like the error shows.

I'm not an expert with codeIgniter, but here's how to do a normal query directly, that's probably what you want to do:

function check_existing_User_weightStatus($u_id)
 {
    $today = date('Y-m-d');
    $this->load->database();
    $query = $this->db->query("SELECT * FROM `user_weight` WHERE `creater_id` = '$u_id' AND DATE(`created_date`) = '$today'");

    if ($query->num_rows() > 0) {
        return true;
    } else {
        return false;
    } 
 }

The error in your code is occurring at this line

$array = array('creater_id' => $u_id,DATE('created_date') => $today);

I'm pretty sure this is not how the where clause will be done, so you might lookup the codeIgniter docs ! to find the right way to do that ! (You're not telling the where clause to use AND, OR, etc.. operators)

Question 4

When your field in MySQL is Date-time it aspects proper format before saving in database.

So convert string to date time format in php using.

date("Y-m-d H:i:s", strtotime($string));

Or you can do the same in Datepicker

$(function() {
    $('#datepicker').datepicker({
        dateFormat: 'yy-dd-mm',
        onSelect: function(datetext){
            var d = new Date(); // for now
            var h = d.getHours();
                h = (h < 10) ? ("0" + h) : h ;

                var m = d.getMinutes();
            m = (m < 10) ? ("0" + m) : m ;

            var s = d.getSeconds();
            s = (s < 10) ? ("0" + s) : s ;

                datetext = datetext + " " + h + ":" + m + ":" + s;
            $('#datepicker').val(datetext);
        },
    });
});

Fiddle for datepicker

Note if you want date only in MySQL field the omit hour min sec part of conversion

Question 5

You do not need to use a row generator to enumerate every day to get the number of week days - it can be done using a simple calculation:

From my answer here:

CREATE FUNCTION getWorkingDays (
  in_start_date IN  DATE,
  in_end_date   IN  DATE
) RETURN NUMBER DETERMINISTIC
IS
  p_start_date   DATE;
  p_end_date     DATE;
  p_working_days NUMBER;
  p_holiday_days NUMBER;
BEGIN
  IF in_start_date IS NULL OR in_end_date IS NULL THEN
    RETURN NUll;
  END IF;

  p_start_date := LEAST( in_start_date, in_end_date );
  p_end_date   := GREATEST( in_start_date, in_end_date );

  -- 5/7 * ( Number of days between monday of the week containing the start date
  --         and monday of the week containing the end date )
  -- + LEAST( day of week for end date, 5 )
  -- - LEAST( day of week for start date, 5 )
  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ), 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ), 5 );

  SELECT COALESCE(
           SUM(
             LEAST( p_end_date, holiday_date + INTERVAL '1' DAY )
             - GREATEST( p_start_date, holiday_date )
           ),
           0
         )
  INTO   p_holiday_days
  FROM   Holidays
  WHERE  HOLIDAY_DATE BETWEEN TRUNC( p_start_date )
                      AND     TRUNC( p_end_date )
  AND    HOLIDAY_DATE - TRUNC( HOLIDAY_DATE, 'IW' ) < 5;

  RETURN GREATEST( p_working_days - p_holiday_days, 0 );
END;
/

Question 6

plot_date does a trick, it converts dates to number of days since 1-1-1 and uses these numbers to plot, then converts the ticks to dates again in order to draw nice tick labels. So using plot_date each day count as 1, business or not.

You can plot your data against a uniform range of numbers but if you want dates as tick labels you need to do it yourself.

d = pd.date_range(start="1/1/2012", end="2/1/2012", freq="B")
v = np.linspace(1,10,len(d))

plt.plot(range(d.size), v)
xticks = plt.xticks()[0]
xticklabels = [(d[0] + x).strftime('%Y-%m-%d') for x in xticks.astype(int)]
plt.xticks(xticks, xticklabels)
plt.autoscale(True, axis='x', tight=True)

But be aware that the labels can be misleading. The segment between 2012-01-02 and 2012-01-09 represents five days, not seven.

Day difference without weekends

Answers

php,codeigniter,date