fix database timezone

[alttp.git] / database / migrations / 2024_04_04_092522_fix_database_timezone_like_wtf_really.php

diff --git a/database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php b/database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php
new file mode 100644 (file)
index 0000000..688624d
--- /dev/null
+++ b/database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php
@@ -0,0 +1,190 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\DB;
+
+return new class extends Migration
+{
+
+       public static $timestamp_fields = [
+               'alttp_seeds' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'aos_seeds' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'applications' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'channels' => [
+                       'created_at',
+                       'updated_at',
+                       'guessing_start',
+                       'guessing_end',
+               ],
+               'channel_crews' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'chat_logs' => [
+                       'created_at',
+                       'updated_at',
+                       'evaluated_at',
+               ],
+               'discord_bot_commands' => [
+                       'executed_at',
+                       'created_at',
+                       'updated_at',
+               ],
+               'discord_channels' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'discord_guilds' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'discord_roles' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'episodes' => [
+                       'start',
+                       'created_at',
+                       'updated_at',
+               ],
+               'episode_crews' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'episode_players' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'events' => [
+                       'start',
+                       'end',
+                       'created_at',
+                       'updated_at',
+               ],
+               'failed_jobs' => [
+                       'failed_at',
+               ],
+               'guessing_guesses' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'guessing_winners' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'organizations' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'participants' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'password_resets' => [
+                       'created_at',
+               ],
+               'personal_access_tokens' => [
+                       'last_used_at',
+                       'created_at',
+                       'updated_at',
+               ],
+               'protocols' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'results' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'rounds' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'techniques' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'technique_chapter' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'technique_maps' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'technique_relations' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'technique_translations' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'tournaments' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'twitch_bot_commands' => [
+                       'executed_at',
+                       'created_at',
+                       'updated_at',
+               ],
+               'twitch_tokens' => [
+                       'created_at',
+                       'updated_at',
+               ],
+               'users' => [
+                       'created_at',
+                       'updated_at',
+                       'avatar_cached',
+               ],
+       ];
+
+       /**
+        * Run the migrations.
+        *
+        * @return void
+        */
+       public function up() {
+               foreach (static::$timestamp_fields as $table => $fields) {
+                       $updates = [];
+                       foreach ($fields as $field) {
+                               $updates[$field] = DB::raw('CONVERT_TZ(`'.$field.'`, \'+00:00\', \'SYSTEM\')');
+                       }
+                       try {
+                               DB::table($table)->update($updates);
+                       } catch (\Exception $e) {
+                               echo $e->getMessage(), PHP_EOL;
+                       }
+               }
+       }
+
+       /**
+        * Reverse the migrations.
+        *
+        * @return void
+        */
+       public function down() {
+               foreach (static::$timestamp_fields as $table => $fields) {
+                       $updates = [];
+                       foreach ($fields as $field) {
+                               $updates[$field] = DB::raw('CONVERT_TZ(`'.$field.'`, \'SYSTEM\', \'+00:00\')');
+                       }
+                       try {
+                               DB::table($table)->update($updates);
+                       } catch (\Exception $e) {
+                               echo $e->getMessage(), PHP_EOL;
+                       }
+               }
+       }
+};