User-Specific Timezone Support

[stuartc]

Feature Request: User-Specific Timezone Support

Is your feature request related to a problem? Please describe.

Currently, all timestamps in Lightning are displayed in UTC across the entire application. This creates confusion for users working in different timezones, as reported in issue #3620. When a user in Africa/Nairobi (UTC+3) initiates an export at 09:55 EAT, both the email notification and exported sheet display 06:55 (UTC), appearing ~3 hours behind their local time.

This feature would enable users to see timestamps in their local timezone throughout the application, improving usability and reducing timezone-related confusion. Users working across different timezones would benefit from seeing times relevant to their context, while maintaining UTC in the database for consistency.

Tell us about your idea - describe the solution you'd like.

Add comprehensive timezone support that allows users to set their preferred timezone and see all timestamps displayed in that timezone throughout Lightning. The solution should:

1. Store user timezone preferences in the database
2. Convert all displayed timestamps from UTC to the user's timezone
3. Handle timezone conversion in date range queries and filters
4. Display timestamps in user's timezone in emails
5. Include timezone information in exported data

Implementation Areas

The following areas would require changes to implement timezone support:

Database Schema

• Add timezone field to User schema
• Create migration to add timezone column to users table with default value (e.g., "Etc/UTC")

Backend Presentation Logic

• Update format_date/2 and format_date_long/1 helper functions to accept user timezone and convert from UTC
• Update datetime component to pass user timezone to backend formatting and LocalTimeConverter hook
• Update timestamp display components across all LiveViews (runs, workflows, dashboards)
• Add timezone selector to user profile preferences

Email Notifications

• Update export completion email to format timestamps in user's timezone
• Update project digest email to format date ranges in user's timezone
• Update account deletion notification to format deletion date in user's timezone
• Update project deletion notification to format deletion date in user's timezone
• Update Kafka trigger failure email to format timestamps in user's timezone
• Ensure digest email worker calculates and displays date ranges in user's timezone
• Update failure notification timestamps

Data Export

• Include timezone information in exported JSON metadata
• Convert or annotate log file timestamps with timezone
• Include timezone context for exported work order, run, and step timestamps

Search and Query Handling

• Convert user-provided date filters from their timezone to UTC for database queries
• Update workorder search queries to handle timezone conversion
• Update date range filters in history views to convert between user timezone and UTC
• Update date picker UI to show user's timezone

Frontend/JavaScript

• Update LocalTimeConverter hook to use user's stored timezone preference instead of browser's local time
• Add timezone parameter to date formatting utilities
• Ensure React components that display timestamps receive and handle timezone context

Phoenix/Timex Integration

• Leverage Timex library with timezone support
• Update all Calendar.strftime/2 calls to include timezone information
• Use DateTime.shift_zone/2 for timezone conversion
• Ensure proper handling of daylight saving time transitions

User Interface

• Add timezone selector to user profile settings
• Display current timezone in UI (footer or header)
• Show timezone abbreviation next to timestamps (e.g., "09:55 EAT" or "06:55 UTC")
• Update date picker components to work with user's timezone

Testing and Performance

• Verify Ecto/PostgreSQL handle timezone conversion gracefully with AT TIME ZONE clause
• Test performance impact of timezone conversions in large result sets
• Ensure database indexes remain effective with timezone-aware queries
• Test daylight saving time transitions

Additional context

This is a substantial feature that touches many parts of the application. The work should be broken down into phases:

Phase 1: Foundation

• Add timezone field to User schema
• Update helper functions for timezone-aware formatting
• Basic UI for timezone selection

Phase 2: Core Display

• Update all LiveView timestamp displays
• Update JavaScript hooks for client-side timezone handling
• Ensure consistency across the application

Phase 3: Queries and Search

• Update search parameters to convert timezone-aware filters to UTC
• Test query performance
• Update date range pickers

Phase 4: Notifications and Export

• Update all email templates
• Update export worker to include timezone information
• Test exported data integrity

Phase 5: Polish

• Comprehensive testing across timezones
• Daylight saving time transition testing
• Documentation updates
• User guide for timezone settings

The current browser-side LocalTimeConverter hook provides basic local time display in the UI, but without backend awareness, emails, exports, and server-side functionality remain UTC-only. A complete solution requires both frontend and backend timezone awareness.