A CASE for Eloquent

In 2014, our development group at Mass Enthusiasm switched PHP frameworks from CodeIgniter to Laravel for building web applications. CodeIgniter served us (and myself) very faithfully for several years, but it was time to move on to something more modern which also helped enforce OOP techniques.

Our first opportunity to leverage the newfound elegance in Laravel came in a project designed to streamline valet parking by remote agents at various locations in different time zones. Each agent carried a smartphone used to scan a location-specific QR coded ticket which was then assigned to a vehicle.

The project deliverables included a console used to manage users, locations, ticket ranges and the like. Additionally, an administrator would run searches and generate reports in Excel format. (We used the excellent Laravel Excel for this purpose.)

In an effort to keep the application's database schema as simple as possible, we opted for a single timezone field on the locations table. The key, however, was to normalize the date stamps (tickets.created_at) across time zones so that a report or ticket search would return results in the local time zone, not the time zone of the administrator's client computer. We achieved this using a neat little MySQL CASE statement.

$query = DB::table('tickets')
		->join('locations', 'tickets.location_id', '=', 'locations.id')
		->join('customers', 'locations.customer_id', '=', 'customers.id')
		->select(
				'tickets.*', 
				'locations.location_name', 
				'customers.customer_name',
				DB::raw("(CASE 
						WHEN locations.timezone = 'America/New_York' THEN date_sub(tickets.created_at, interval 5 hour)
						WHEN locations.timezone = 'America/Chicago' THEN date_sub(tickets.created_at, interval 6 hour)
						WHEN locations.timezone = 'America/Denver' THEN date_sub(tickets.created_at, interval 7 hour)
						WHEN locations.timezone = 'America/Los_Angeles' THEN date_sub(tickets.created_at, interval 8 hour)
						ELSE NULL END) 
						AS created_at_adjusted"
						)
				)->orderby('tickets.created_at', 'desc');

Once we had all of this in place, we ran into a kink. A search form submitted against the tickets table would need to include a date range. Any query using Eloquent's whereBetween() method would need to use the time zone-adjusted datetime stamp (tickets.created_at_adjusted) in it's predicate. Sadly, aliased columns cannot be used in SQL's WHERE clause.

In the end, we added a second column to the locations table containing the respective UTC time zone adjustment value. At that point, our beloved CASE statement became obsolete.

$query = DB::table('tickets')
		->join('locations', 'tickets.location_id', '=', 'locations.id')
		->join('customers', 'locations.customer_id', '=', 'customers.id')
		->select(
				'tickets.*', 
				'locations.location_name', 
				'customers.customer_name',
				'locations.utc_offset',
				DB::raw('date_sub(tickets.created_at, interval locations.utc_offset hour) as created_at_adjusted')
				)->orderby('tickets.created_at', 'desc');