Custom timezone support in Laravel/Lumen

Question

If I'm building a timezone aware Laravel (Lumen) app, where the user may not have control over their database timezone settings, but can provide my app with a custom timezone in the config.

I also need to be able to store timestamps in the database via a direct connection in a third party app (again remember the timezone may be incorrect in the database config) so I'm thinking a unix timestamp would be appropriate so I can use UNIX_TIMESTAMP(NOW()) in my third party direct connection.

1. Should I be storing timestamps in unix integer format?

2. What is the best way to support this globally in Laravel since $table->timestamps(); uses TIMESTAMP

3. How do I ensure date/times are automatically converted to the timezone specified in the Laravel config when I query the database to output json to an API?

Note: I am not looking to translate to multiple timezones, just the one in the .env

Current .env

APP_TIMEZONE=UTC

Current migration example

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateSharesTable extends Migration
{
    public function up()
    {
        Schema::create('shares', function (Blueprint $table) {
            $table->increments('id');

            $table->string('url', 500);
            $table->string('description', 100);
            $table->integer('hits');
            $table->string('ip_address', 39);

            $table->timestamps();
        });
    }

Current Share model

namespace App;

use Illuminate\Database\Eloquent\Model;


class Share extends Model
{

    protected $fillable = ['url', 'description', 'ip_address'];

    protected $hidden = ['ip_address'];
}

Current controller example

namespace App\Http\Controllers;

use App\Share;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;

class ShareController extends Controller
{

    /**
     * @SWG\Get(
     *     tags={"Shares"},
     *     path="/shares",
     *     summary="Finds all shares",
     *     description="Returns all shares ",
     *     @SWG\Response(
     *         response=200,
     *         description="A list of all shares"
     *     )
     * )
     */
    public function fetchAll()
    {

        return Share::get();
    }

Answer 1

You can use config\app.php instead of .env

config(['app.timezone' => $timezone]);

where $timezone is one of these: http://php.net/manual/en/timezones.php

Should I be storing timestamps in unix integer format?

You could, but iso8601 is litterally an ISO standard, so why not that

What is the best way to support this globally in Laravel since $table->timestamps(); uses TIMESTAMP

Carbon is a nice way to work with timestamps, adding and substracting time. Use my code above to change the timezones and use them in Carbon.

How do I ensure date/times are automatically converted to the timezone specified in the Laravel config when I query the database to output json to an API?

That's what Laravel will handle for you if you set the timezone in a session that can be kept between requests or if you pass it alang as an argument every request.