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.
Latest comments (0)