jreikes jreikes - 7 months ago 59
PHP Question

Getting now() from database server in Laravel 5 to get around AWS clock drift

I'm building a platform where precise time sync is critical (+/- 0.01 sec). Based on my (admittedly limited) understanding of AWS, it looks like clock drift between multiple server instances could become a real problem. However, I've been told RDS will not suffer from clock drift even as it scales up. As a result, I'm planning to use RDS when we go live and am trying to always pull now() with a database query instead of just using Carbon::now() (which would pull time from the Apache server). So I guess the preliminary question is: have I missed anything? Is that going to work? Is there a better approach that I've ignored?

Assuming my approach is correct, I'm trying to clean up the following:

$currentTime = DB::select(DB::raw('SELECT NOW()'));


First off, that just looks messy and I'm sure there's a cleaner way. Second, instead of giving me the timestamp, it's returning an array that looks like this:

Array
(
[0] => stdClass Object
(
[NOW()] => 2016-05-04 14:03:49
)
)


I tried using ->first() and ->get(), but neither worked (both produce errors). How should I be handling this?

Answer

Turns out this is a trickier deal than I originally anticipated. Laravel uses PDO to connect to MySQL. Though MySQL now supports DATETIME with up to 6 places of precision after the decimal, PDO does not support fractions of a second in DATETIME (yeah, "WTF?" is the right response). To retrieve and store data with the precision I'm after, I needed to change gears and use Unix time format (seconds since 1/1/1970). I'm storing that in the database as a DECIMAL(18,3) column to give me room for the millisecond precision I was after.

The code section I described above is now:

$serverTimeArray = DB::select(DB::raw('SELECT UNIX_TIMESTAMP(UTC_TIMESTAMP(3)) AS now'));
$serverTime = $serverTimeArray[0]->now;

This actually makes a lot of my time manipulations (add/subtract time) quite a bit easier.

Seems to be working, though I now have a new, unrelated issue I might need to post a different question about...