DEV Community

Dimitrios Desyllas
Dimitrios Desyllas

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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 
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

Must be written:

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

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

Top comments (0)