The Midnight Billing Bug
🌙 The Crime
Billing systems that process charges at midnight face a fundamental problem: which midnight? When customers span multiple timezones, "end of day" billing can charge the same customer multiple times, miss charges entirely, or bill for the wrong day. The result is revenue leakage, customer complaints, and accounting nightmares.
🌍 The Global Midnight Problem
🗾 Tokyo Customer
🇬🇧 London Customer
🗽 New York Customer
🔍 Real-World Disasters
☁️ Cloud Provider Chaos
A major cloud provider billed customers in their local timezone, causing some customers to be charged 25 hours for a 24-hour day during DST transitions, while others got 23 hours.
📱 Mobile App Subscriptions
A subscription app processed renewals at midnight local time, causing customers traveling across timezones to be charged twice in the same day or skip charges entirely.
🏨 Hotel Booking System
A hotel chain's booking system charged cancellation fees based on server timezone instead of hotel timezone, leading to incorrect "late cancellation" charges.
💳 Credit Card Processing
A payment processor's daily settlement ran at midnight UTC, causing transactions to appear on the wrong day for customers in different timezones.
🔄 Common Billing Patterns & Problems
❌ Pattern 1: Server Timezone Billing
Bill all customers at midnight server time (usually UTC).
Problems:
- • Customers billed at random times
- • Poor user experience
- • Confusion about billing dates
- • Support ticket overload
Example:
⚠️ Pattern 2: Customer Timezone Billing
Bill each customer at midnight in their local timezone.
Problems:
- • 24+ billing runs per day
- • Complex scheduling
- • DST transition issues
- • Timezone changes by customers
Example:
✅ Pattern 3: Business Day Billing
Bill based on business rules, not arbitrary midnight times.
Benefits:
- • Predictable billing cycles
- • Clear business logic
- • Timezone-independent
- • Easier to explain to customers
Example:
💻 Code Examples
❌ Problematic Billing Code
// JavaScript - Dangerous midnight billing
function processDailyBilling() {
const now = new Date();
// This uses server timezone - wrong!
if (now.getHours() === 0 && now.getMinutes() === 0) {
billAllCustomers();
}
}
// SQL - Naive date comparison
SELECT customer_id, SUM(usage)
FROM usage_logs
WHERE DATE(created_at) = CURRENT_DATE -- Server timezone!
GROUP BY customer_id;
-- This will miss or double-count usage across timezones✅ Solution 1: UTC-Based Billing Periods
// JavaScript - Clear UTC-based billing
function calculateBillingPeriod(customerSignupDate) {
const signup = new Date(customerSignupDate);
const now = new Date();
// Calculate complete 30-day periods since signup
const daysSinceSignup = Math.floor((now - signup) / (1000 * 60 * 60 * 24));
const completePeriods = Math.floor(daysSinceSignup / 30);
return {
periodStart: new Date(signup.getTime() + (completePeriods * 30 * 24 * 60 * 60 * 1000)),
periodEnd: new Date(signup.getTime() + ((completePeriods + 1) * 30 * 24 * 60 * 60 * 1000)),
periodNumber: completePeriods + 1
};
}
-- SQL - UTC-based usage aggregation
SELECT
customer_id,
SUM(usage) as total_usage,
DATE_TRUNC('day', created_at AT TIME ZONE 'UTC') as usage_date_utc
FROM usage_logs
WHERE created_at >= $1 AND created_at < $2 -- Explicit UTC range
GROUP BY customer_id, usage_date_utc;✅ Solution 2: Customer Timezone Aware Billing
// JavaScript - Proper timezone handling
function getCustomerBillingDay(customerId, customerTimezone) {
const now = new Date();
// Convert to customer's timezone
const customerTime = new Intl.DateTimeFormat('en-CA', {
timeZone: customerTimezone,
year: 'numeric',
month: '2-digit',
day: '2-digit'
}).format(now);
return customerTime; // YYYY-MM-DD in customer timezone
}
function shouldBillCustomer(customer) {
const customerDay = getCustomerBillingDay(customer.id, customer.timezone);
const lastBilledDay = customer.last_billed_date;
// Bill if it's a new day in customer's timezone
return customerDay !== lastBilledDay;
}
-- SQL - Timezone-aware daily aggregation
SELECT
customer_id,
customers.timezone,
SUM(usage) as daily_usage,
(created_at AT TIME ZONE 'UTC' AT TIME ZONE customers.timezone)::date as customer_date
FROM usage_logs
JOIN customers ON customers.id = usage_logs.customer_id
WHERE created_at >= NOW() - INTERVAL '2 days' -- Buffer for timezone differences
GROUP BY customer_id, customers.timezone, customer_date;✅ Solution 3: Billing Period Tracking
-- SQL - Explicit billing periods table
CREATE TABLE billing_periods (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
period_start TIMESTAMP WITH TIME ZONE NOT NULL,
period_end TIMESTAMP WITH TIME ZONE NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Generate billing periods in advance
INSERT INTO billing_periods (customer_id, period_start, period_end)
SELECT
id as customer_id,
generate_series(
signup_date,
NOW() + INTERVAL '1 year',
INTERVAL '1 month'
) as period_start,
generate_series(
signup_date + INTERVAL '1 month',
NOW() + INTERVAL '1 year' + INTERVAL '1 month',
INTERVAL '1 month'
) as period_end
FROM customers
WHERE billing_type = 'monthly';
-- Bill based on periods, not arbitrary dates
SELECT bp.*, SUM(ul.amount) as total_usage
FROM billing_periods bp
JOIN usage_logs ul ON ul.customer_id = bp.customer_id
AND ul.created_at >= bp.period_start
AND ul.created_at < bp.period_end
WHERE bp.status = 'pending'
AND bp.period_end <= NOW()
GROUP BY bp.id;🛡️ Prevention Strategies
📅 Billing Design
- • Use explicit billing periods
- • Avoid midnight-based triggers
- • Store customer timezone preferences
- • Design timezone-agnostic billing rules
🗄️ Data Storage
- • Always store UTC timestamps
- • Include timezone info in records
- • Use explicit date ranges
- • Avoid date-only fields for billing
🧪 Testing
- • Test with customers in all timezones
- • Simulate DST transitions
- • Test timezone changes
- • Validate billing period boundaries
📊 Monitoring
- • Monitor billing run durations
- • Alert on unusual billing amounts
- • Track customer timezone distribution
- • Validate revenue consistency
🎓 Lessons Learned
"Midnight is not a global constant."
The Midnight Billing Bug teaches us that time-based business logic must account for the global nature of modern applications. What seems like a simple "end of day" calculation becomes complex when customers span the globe.
Key Takeaway: Design billing systems around business logic, not arbitrary time boundaries. Use explicit periods, store everything in UTC, and always consider the customer's perspective on time.
🔗 Related Time Crimes
📢 Share Your Billing Horror Story
Have you been burned by midnight billing bugs? Share your experience to help others avoid the same fate.