PHP+jQuery DataTable实时计算剩余配送天数方案问询

Got it, let's fix this real-time remaining days issue in your jQuery DataTable. The root problem right now is that you're calculating the day difference on the server side (PHP) when the page loads—so it's a static value that doesn't update as time passes. We need to shift this calculation to the client side with JavaScript, which can compute the difference based on the current Dubai timezone date every time the table renders or refreshes.

Step 1: Prepare Your Data Source #

First, make sure your DataTable pulls the raw delivery date (in Y-m-d format) from your database, not the pre-calculated day difference. We'll handle the math on the front end.

Step 2: Implement Client-Side Calculation in DataTable #

If you're loading data via AJAX (the most common DataTable setup), use the render function in your column definition to dynamically compute the remaining days. We'll also handle the Asia/Dubai timezone to avoid mismatches with the user's local time.

Here's a complete example:

<table id="deliveryTable" class="display" style="width:100%">
    <thead>
        <tr>
            <th>Order ID</th>
            <th>Entry Date</th>
            <th>Delivery Date</th>
            <th>D/Left</th>
        </tr>
    </thead>
</table>

<script>
$(document).ready(function() {
    $('#deliveryTable').DataTable({
        ajax: {
            url: 'your-data-fetch.php', // Replace with your PHP endpoint that returns raw order data
            dataSrc: ''
        },
        columns: [
            { data: 'order_id' },
            { data: 'entry_date' },
            { data: 'delivery_date' },
            { 
                data: 'delivery_date',
                render: function(deliveryDateStr) {
                    if (!deliveryDateStr) return '-';

                    // Get current date in Asia/Dubai timezone, stripped of time values
                    const dubaiTimezone = 'Asia/Dubai';
                    const currentDate = new Date(new Date().toLocaleString('en-US', { timeZone: dubaiTimezone }));
                    currentDate.setHours(0, 0, 0, 0); // Reset time to midnight for accurate day comparison

                    // Parse delivery date and convert to Dubai timezone
                    const deliveryDate = new Date(deliveryDateStr);
                    const deliveryDateDubai = new Date(deliveryDate.toLocaleString('en-US', { timeZone: dubaiTimezone }));
                    deliveryDateDubai.setHours(0, 0, 0, 0);

                    // Calculate days left
                    const timeDiff = deliveryDateDubai.getTime() - currentDate.getTime();
                    const daysLeft = Math.ceil(timeDiff / (1000 * 3600 * 24));

                    // Format output based on status
                    if (daysLeft < 0) {
                        return `<span style="color: #dc3545;">Overdue by ${Math.abs(daysLeft)} days</span>`;
                    } else if (daysLeft === 0) {
                        return `<span style="color: #fd7e14;">Today</span>`;
                    } else {
                        return `<span style="color: #28a745;">${daysLeft} days left</span>`;
                    }
                }
            }
        ],
        // Optional: Auto-refresh the table hourly to update days without page reload
        initComplete: function() {
            setInterval(() => {
                $('#deliveryTable').DataTable().ajax.reload(null, false); // false = keep current pagination
            }, 3600000); // 1 hour in milliseconds
        }
    });
});
</script>

If You're Using Static HTML Rows #

If your table is rendered directly in HTML (not AJAX), add a data-delivery-date attribute to the delivery date cells, then use a function to update the "D/Left" column periodically:

<table id="deliveryTable" class="display" style="width:100%">
    <thead>
        <tr>
            <th>Order ID</th>
            <th>Entry Date</th>
            <th>Delivery Date</th>
            <th>D/Left</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1001</td>
            <td>2020-05-18</td>
            <td data-delivery-date="2020-05-20">2020-05-20</td>
            <td class="days-left"></td>
        </tr>
        <!-- More rows here -->
    </tbody>
</table>

<script>
function updateDaysLeft() {
    const dubaiTimezone = 'Asia/Dubai';
    const currentDate = new Date(new Date().toLocaleString('en-US', { timeZone: dubaiTimezone }));
    currentDate.setHours(0, 0, 0, 0);

    $('#deliveryTable tbody tr').each(function() {
        const deliveryDateStr = $(this).find('[data-delivery-date]').data('delivery-date');
        if (!deliveryDateStr) return;

        const deliveryDate = new Date(deliveryDateStr);
        const deliveryDateDubai = new Date(deliveryDate.toLocaleString('en-US', { timeZone: dubaiTimezone }));
        deliveryDateDubai.setHours(0, 0, 0, 0);

        const timeDiff = deliveryDateDubai.getTime() - currentDate.getTime();
        const daysLeft = Math.ceil(timeDiff / (1000 * 3600 * 24));

        // Update the D/Left cell
        let displayText;
        if (daysLeft < 0) {
            displayText = `<span style="color: #dc3545;">Overdue by ${Math.abs(daysLeft)} days</span>`;
        } else if (daysLeft === 0) {
            displayText = `<span style="color: #fd7e14;">Today</span>`;
        } else {
            displayText = `<span style="color: #28a745;">${daysLeft} days left</span>`;
        }
        $(this).find('.days-left').html(displayText);
    });
}

// Run on page load and refresh hourly
$(document).ready(function() {
    updateDaysLeft();
    setInterval(updateDaysLeft, 3600000);
});
</script>

Key Notes #

• Timezone Handling: We explicitly use Asia/Dubai in toLocaleString to ensure the current date matches your server's timezone, avoiding errors from users in different regions.
• Date Normalization: Resetting dates to midnight ensures we count full days, not partial ones based on time of day.
• Auto-Refresh: The setInterval call keeps the values up-to-date without requiring users to reload the page. Adjust the interval (in milliseconds) if you need more frequent updates.

内容的提问来源于stack exchange，提问作者Roshan Asghar