Refactor time convertion from postgresql to php.

In my application I had the following query:

select 
  appointment.doctor_id,
  appointment.date,
  date_part('epoch',appointment.date::DATE + schedule.time_zero - INTERVAL '2 hours')::INT AS date_that_should_have_booked_in_unix_time
from
  appointment
join
  schedule on appointment.schedule_id = schedule.id
order by date asc

With the query above I retrieve the overbooked appointments for a doctor's schedule. I wanted to refactor the part of the query:

date_part('epoch',appointment.date::DATE + schedule.time_zero - INTERVAL '2 hours')::INT

Into php, then I will run a test to ensure that the refactored code follows the original query.

The test is:

use Carbon/Carbon;
use PDO;

//Code here with initialized database connection and appropriate mocks

// Instead of fixed string assume that values come from db
$date_that_should_have_booked_in_unix_time = new Carbon('2021-12-10')->setTime('13:00:00');

$sql =<<<SQL
select 
  appointment.doctor_id,
  appointment.date,
  date_part('epoch',appointment.date::DATE + schedule.time_zero - INTERVAL '2 hours')::INT AS date_that_should_have_booked_in_unix_time
from
  appointment
join
  schedule on appointment.schedule_id = schedule.id
order by date asc
SQL;

/**
* @var PDO $dbh
*/
$result = $dbh->query($sql);

$this->assertEquals($result->date_that_should_have_booked_in_unix_time,$date_that_should_have_booked_in_unix_time->unix());

//Yes some other code here

As you will see the test will fail. A reason why is because Carbon's takes by default local time zone and before converting the time into unix time it converts it first into UTC time.

What we need to do is specifically to set the time and date into UTC regardless the time zone so the test above to work.

Therefore the line of code above:

$date_that_should_have_booked_in_unix_time = new Carbon('2021-12-10')->setTime('13:00:00');

Must be written:

$date_that_should_have_booked_in_unix_time = new Carbon('2021-12-10','UTC')->setTime('13:00:00');

So we bypass the timezone convertion and we get the local time into appropriate unix time.

Blog

Refactor time convertion from postgresql to php.

Dimitrios Desyllas

Join Our Newsletter. No Spam, Only the good stuff.

Related