// Store dates and time into separate database tables and reference them // whenever you need to add a time or date field // Easy lookups

CreateDateDimensionTable.php

<?php

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

class CreateDateDimensionTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('dim_date', function (Blueprint $table) {
            $table->unsignedInteger('datekey')->primary();
            $table->date('datekey_full');
            $table->unsignedInteger('day');
            $table->unsignedInteger('month');
            $table->unsignedInteger('year');
            $table->string('day_name');
            $table->string('day_suffix', 2);
            $table->unsignedInteger('day_of_week');
            $table->unsignedInteger('day_of_year');
            $table->unsignedInteger('is_weekend');
            $table->unsignedInteger('week');
            $table->unsignedInteger('iso_week');
            $table->unsignedInteger('week_of_month');
            $table->unsignedInteger('week_of_year');
            $table->unsignedInteger('iso_week_in_year');
            $table->string('month_name');
            $table->string('month_year');
            $table->string('month_name_year');
            $table->date('first_day_of_month');
            $table->date('last_day_of_month');
            $table->date('first_day_of_next_month');
            $table->unsignedInteger('quarter');
            $table->string('quarter_name');
            $table->date('first_day_of_quarter');
            $table->date('last_day_of_quarter');
            $table->date('first_day_of_year');
            $table->date('last_day_of_year');
            $table->date('first_day_of_next_year');
            $table->unsignedInteger('dow_in_month');
            $table->timestamps();
        });

        Artisan::call('dim:populate:date');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('dim_date');
    }
}

CreateTimeDimensionTable.php

<?php

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

class CreateTimeDimensionTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('dim_time', function (Blueprint $table) {
            $table->string('timekey')->primary();
            $table->string('timekey_alt');
            $table->unsignedInteger('h');
            $table->unsignedInteger('m');
            $table->unsignedInteger('s');
            $table->string('part_of_day');
            $table->string('part_of_day_group');
            $table->string('part_of_day_half');
            $table->string('part_of_day_half_group');
            $table->string('timezone');
            $table->timestamps();
        });

        Artisan::call('dim:populate:time');

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('dim_time');
    }
}

DateDimension.php

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class DateDimension extends Model
{
    /**
     * Indicates if the IDs are auto-incrementing.
     *
     * @var bool
     */
    public $incrementing = false;

    /**
     * The "type" of the primary key ID.
     *
     * @var string
     */
    protected $keyType = 'int';

    /**
     * The primary key associated with the table.
     *
     * @var string
     */
    protected $primaryKey = 'date_key';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'date_key',
        'date_full',
        'day',
        'month',
        'year',
        'day_name',
        'day_suffix',
        'day_of_week',
        'day_of_year',
        'is_weekend',
        'week',
        'iso_week',
        'week_of_month',
        'week_of_year',
        'iso_week_in_year',
        'month_name',
        'month_year',
        'month_name_year',
        'first_day_of_month',
        'last_day_of_month',
        'first_day_of_next_month',
        'quarter',
        'quarter_name',
        'first_day_of_quarter',
        'last_day_of_quarter',
        'first_day_of_year',
        'last_day_of_year',
        'first_day_of_next_year',
        'dow_in_month',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'date_full'               => 'date_full:Y-m-d',
        'first_day_of_month'      => 'date_full:Y-m-d',
        'last_day_of_month'       => 'date_full:Y-m-d',
        'first_day_of_next_month' => 'date_full:Y-m-d',
        'first_day_of_quarter'    => 'date_full:Y-m-d',
        'last_day_of_quarter'     => 'date_full:Y-m-d',
        'first_day_of_year'       => 'date_full:Y-m-d',
        'last_day_of_year'        => 'date_full:Y-m-d',
        'first_day_of_next_year'  => 'date_full:Y-m-d',
    ];
}

DimensionPopulateDate.php

<?php

namespace App\Console\Commands;

use App\Libraries\BA\Analyse\Memory;
use Carbon\Carbon;

use Carbon\CarbonPeriod;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\LazyCollection;
use function ceil;
use function now;

class DimensionPopulateDate extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'dim:populate:date';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Populate the date dimension table';

    public function handle()
    {
        DB::table('dim_date')->truncate();
        $bar = $this->output->createProgressBar();
        $bar->setFormat("<fg=magenta>[memory]: %custom_memory%:%memory% [elapsed]: %elapsed%</> \n<info>%current%/%max%</info> <comment>[%bar%]</comment>");
        $bar->setMessage(Memory::getUsage(), 'custom_memory');
        Memory::start();

        LazyCollection::make(function () use ($bar) {
            $dates = CarbonPeriod::create('1970-01-01', '2040-12-31');
            $bar->start($dates->count());
            foreach ($dates as $date) {
                // Get the quarter details, as ABC has a different quarter system
                // @note - Carbon does not allow to override the quarters
                $quarterDetails = $this->getQuarterDetails($date);

                // Main transformer
                yield [
                    'datekey' => (int) $date->format('Ymd'),
                    'datekey_full' => $date->format('Y-m-d'),
                    'day' => $date->day,
                    'month' => $date->month,
                    'year' => $date->year,
                    'day_name' => $date->dayName,
                    'day_suffix' => $this->getDaySuffix($date->day),
                    'day_of_week' => $date->dayOfWeek,
                    'day_of_year' => $date->dayOfYear,
                    'is_weekend' => (int) $date->isWeekend(),
                    'week' => $date->week,
                    'iso_week' => $date->isoWeek,
                    'week_of_month' => $date->weekOfMonth,
                    'week_of_year' => $date->weekOfYear,
                    'iso_week_in_year' => $date->isoWeeksInYear,
                    'month_name' => $date->monthName,
                    'month_year' => $date->format('mY'),
                    'month_name_year' => $date->format('MY'),
                    'first_day_of_month' => $date
                        ->clone()
                        ->firstOfMonth()
                        ->format('Y-m-d'),
                    'last_day_of_month' => $date
                        ->clone()
                        ->lastOfMonth()
                        ->format('Y-m-d'),
                    'first_day_of_next_month' => $date
                        ->clone()
                        ->addMonthNoOverflow()
                        ->firstOfMonth()
                        ->format('Y-m-d'),
                    'quarter' => $quarterDetails['value'],
                    'quarter_name' => $quarterDetails['name'],
                    'first_day_of_quarter' => $quarterDetails['first_day_of_quarter'],
                    'last_day_of_quarter' => $quarterDetails['last_day_of_quarter'],
                    'first_day_of_year' => $date
                        ->clone()
                        ->firstOfYear()
                        ->format('Y-m-d'),
                    'last_day_of_year' => $date
                        ->clone()
                        ->lastOfYear()
                        ->format('Y-m-d'),
                    'first_day_of_next_year' => $date
                        ->clone()
                        ->addYear()
                        ->firstOfYear()
                        ->format('Y-m-d'),
                    'dow_in_month' => (int) ceil($date->day / 7),
                    'created_at' => now(),
                    'updated_at' => now()
                ];
            }
        })
            ->chunk(25)
            ->each(function ($chunk) use ($bar) {
                DB::table('dim_date')->insert($chunk->toArray());
                $bar->setMessage(Memory::getUsage(), 'custom_memory');
                $bar->advance(25);
            });

        $bar->setMessage(Memory::stop(), 'custom_memory');
        $bar->finish();
        $this->info(" Finished");
        exit(1);
    }

    /**
     * Get Quarter details
     *
     * - Depending on your companies quarter update the map and logic below
     *
     * @param Carbon $date
     *
     * @return array
     * @throws \Exception
     */
    private function getQuarterDetails(Carbon $date)
    {
        $quarterMonthMap = [
            1 => ['value' => 1, 'name' => 'First'],
            2 => ['value' => 2, 'name' => 'Second'],
            3 => ['value' => 2, 'name' => 'Second'],
            4 => ['value' => 2, 'name' => 'Second'],
            5 => ['value' => 3, 'name' => 'Third'],
            6 => ['value' => 3, 'name' => 'Third'],
            7 => ['value' => 3, 'name' => 'Third'],
            8 => ['value' => 4, 'name' => 'Fourth'],
            9 => ['value' => 4, 'name' => 'Fourth'],
            10 => ['value' => 4, 'name' => 'Fourth'],
            11 => ['value' => 1, 'name' => 'First'],
            12 => ['value' => 1, 'name' => 'First']
        ];

        $output['value'] = $quarterMonthMap[$date->month]['value'];
        $output['name'] = $quarterMonthMap[$date->month]['name'];

        switch ($output['value']) {
            case 1:
                $output['first_day_of_quarter'] = Carbon::parse($date->year - 1 . '-11-01')
                    ->firstOfMonth()
                    ->format('Y-m-d');
                $output['last_day_of_quarter'] = Carbon::parse($date->year . '-01-01')
                    ->lastOfMonth()
                    ->format('Y-m-d');

                break;
            case 2:
                $output['first_day_of_quarter'] = Carbon::parse($date->year . '-02-01')
                    ->firstOfMonth()
                    ->format('Y-m-d');
                $output['last_day_of_quarter'] = Carbon::parse($date->year . '-04-01')
                    ->lastOfMonth()
                    ->format('Y-m-d');

                break;
            case 3:
                $output['first_day_of_quarter'] = Carbon::parse($date->year . '-05-01')
                    ->firstOfMonth()
                    ->format('Y-m-d');
                $output['last_day_of_quarter'] = Carbon::parse($date->year . '-07-01')
                    ->lastOfMonth()
                    ->format('Y-m-d');

                break;
            case 4:
                $output['first_day_of_quarter'] = Carbon::parse($date->year . '-08-01')
                    ->firstOfMonth()
                    ->format('Y-m-d');
                $output['last_day_of_quarter'] = Carbon::parse($date->year . '-10-01')
                    ->lastOfMonth()
                    ->format('Y-m-d');

                break;
        }

        return $output;
    }

    /**
     * Get the Day Suffix
     * Copied logic from - https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/
     *
     * @param $day
     * @return string
     */
    private function getDaySuffix($day)
    {
        if ($day / 10 === 1) {
            return "th";
        }
        $right = substr($day, -1);

        if ($right === 1) {
            return 'st';
        }

        if ($right === 2) {
            return 'nd';
        }

        if ($right === 3) {
            return 'rd';
        }

        return 'th';
    }
}