Time Data Management

Time Interval Storage

How Time Data is Stored and Organized

The time tracking system uses a centralized time_intervals table structure for all time data storage:

Core Time Interval Fields:

started_at and finished_at timestamps for precise time tracking
user_id links time to specific team members
project_id associates time with projects
task_id (optional) for task-specific time tracking
type field indicating tracking method (START, CONTINUE, FINISH, AUTO)
manual_time flag distinguishing manual entries from desktop app tracking

Database Relationships:

Foreign key relationships ensure data integrity with users and projects
Soft delete capabilities for project and user references
Indexed started_at field for efficient date-based queries
Support for multi-currency time tracking through currency_id field

Data Organization:

Time intervals are stored with timezone-aware timestamps
Notes can be attached to time intervals through polymorphic relationships
Screenshot associations link visual proof to time intervals
Team isolation ensures data security and proper access control

Time Calculation Methods

Duration Calculations and Time Formatting

The system employs multiple sophisticated methods for time calculations:

Duration Calculation:

Primary calculation uses TIMESTAMPDIFF(SECOND, started_at, finished_at) for precision
Automatic conversion between seconds, minutes, hours, and days
Support for both decimal hours and HH:MM:SS time formatting
Time-to-hours conversion: hours + (minutes/60) + (seconds/3600)

Time Formatting Options:

SEC_TO_TIME() converts seconds to HH:MM:SS format
TIME_TO_SEC() converts HH:MM:SS to total seconds
Custom formatting for different display contexts (reports, dashboards, invoices)
ISO 8601 duration support for advanced time calculations

Calculation Accuracy:

Second-level precision for all time calculations
Timezone-aware calculations respect team and user preferences
Automatic handling of daylight saving time transitions
Consistent calculation methods across all system components

Time Aggregation

Rolling Up Task Time to Project and Team Levels

The system provides comprehensive time aggregation across multiple organizational levels:

Task-Level Aggregation:

Individual task time totals calculated from associated time intervals
Task time summaries show total time invested per task
Task completion correlation with time investment tracking
Daily and weekly task time analysis for productivity insights

Project-Level Aggregation:

Daily project time totals with real-time updates during tracking
Weekly project time calculations with configurable limits (40-hour default)
Project time percentage distribution across team members
Historical project time analysis for trend identification

Team-Level Aggregation:

Team daily time summaries across all projects and members
Active member counts based on time tracking activity
Team productivity metrics and time allocation analysis
Cross-project time distribution for resource planning

User-Level Aggregation:

Individual user time totals across all assigned projects
Daily and weekly time limits with automatic enforcement
Personal productivity analytics and time tracking patterns
Historical user time data for performance analysis

Historical Time Data

Accessing and Analyzing Historical Time Records

The system maintains comprehensive historical time data with flexible access methods:

Historical Data Access:

Date range queries support any historical period analysis
Efficient database indexing enables fast historical data retrieval
Time interval data preserved even when projects or users are deleted
Comprehensive audit trail for all time tracking activities

Historical Analysis Capabilities:

Time trend analysis across days, weeks, months, and years
Project time evolution tracking for long-term projects
Team productivity trends and seasonal analysis
Comparative analysis between different time periods

Data Retention:

Permanent storage of all time interval records
Screenshot data maintained with associated time intervals
Notes and context preserved for future reference
Time tracking logs maintained for debugging and analysis

Historical Reporting:

Flexible date range selection for custom historical reports
Time aggregation by various periods (daily, weekly, monthly)
Historical project profitability analysis using time and rate data
Team performance trends over extended periods

Time Data Export

Exporting Time Data for External Analysis

The system supports comprehensive time data export capabilities:

Export Formats:

Time data integrated into invoice generation for client billing
Report generation with customizable time period selection
Chart data export for visual analysis and presentations
Team report generation with automated scheduling

Export Scope Options:

Individual user time data export
Project-specific time data extraction
Team-wide time data compilation
Client-specific time data for transparent billing

Automated Export Features:

Daily team reports automatically generated and distributed
Weekly invoice generation from tracked time data
Scheduled report generation for regular business processes
Email delivery of time reports to stakeholders

Data Integration:

Time data feeds directly into invoicing systems
Integration with expense tracking for complete project costing
Rate calculation integration for accurate billing
Multi-currency support for international operations

Data Integrity and Performance

Ensuring Accurate Time Data Management

The system includes robust data integrity and performance optimization features:

Data Integrity Measures:

Transaction-based time entry prevents partial data corruption
Automatic validation of time interval relationships
Constraint enforcement for logical time sequences
Data consistency checks across related records

Performance Optimization:

Strategic database indexing for fast time queries
Efficient aggregation queries minimize system load
Cached calculations for frequently accessed time totals
Optimized date range queries for historical data access

Data Quality Assurance:

Automatic detection and prevention of overlapping time intervals
Validation of time sequence logic (start before end)
Minimum time interval enforcement (10-minute minimum)
Time limit validation (24-hour daily maximum)

Backup and Recovery:

Complete time data preservation in system backups
Point-in-time recovery capabilities for time data
Data migration support for system upgrades
Export capabilities for data portability and backup