Datetimes in mysql are stored as below with a Timezone. These dates as diplayed are stored in Australia/Sydney timezone
# bb_date production_date id
2024-03-16 2022-09-15 14:26:36 248078
2024-03-16 2022-09-15 14:29:51 248079
2024-03-16 2022-09-15 14:34:30 248080
When they arrive in Postgres after being imported with pgLoader they are being stored in UTC
"2024-03-16" "2022-09-15 04:26:36+00" 248078
"2024-03-16" "2022-09-15 04:29:51+00" 248079
"2024-03-16" "2022-09-15 04:34:30+00" 248080
To make the offset correct when they reash postgres you need to set the timezone in the load file for pgloader as follows
Modify the user:password database server IP or hostname, port and DB names
-- file name is named pgload.load
LOAD DATABASE
FROM mysql://dev:[email protected]:4050/sourcedb
INTO postgres://dev:[email protected]:4080/targetdb
set timezone to 'Australia/Sydney'
WITH include drop, create tables
ALTER SCHEMA 'sourcedb' RENAME TO 'public'
BEFORE LOAD DO
$$ DROP SCHEMA public CASCADE; $$
;
I am using a docker container to run pgloader
Run this using by mounting the pgload.load command file into the container and calling pgloader
docker run --rm -it -v "$(pwd)/pgload.load:/pgload.load" dimitri/pgloader:latest \
pgloader /pgload.load
The pgloader migration doesn’t include the AUTO_INCREMENT attribute from MySQL so you need to add a sequence and default value for the id columns
This is an example of achieving it with a CakePHP 4 migration. You can fish the SQL out of this code to run the commands manually in postgres
<?php
declare(strict_types=1);
use Cake\Datasource\ConnectionManager;
use Migrations\AbstractMigration;
class AddSequencesToId extends AbstractMigration
{
/**
* Change Method.
*
* More information on this method is available here:
* https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
* @return void
*/
public function change()
{
$db = ConnectionManager::get('default');
// Create a schema collection.
$collection = $db->getSchemaCollection();
// Get the table names
$tables = $collection->listTables();
// Get a single table (instance of Schema\TableSchema)
// $tableSchema = $collection->describe('posts');
foreach ($tables as $tableName) {
$table = $this->table($tableName);
if (in_array($tableName, ['phinxlog'])) {
continue;
}
$maxId = $this->fetchRow('SELECT MAX(id) as max_id FROM ' . $tableName);
$next = $maxId['max_id'] + 1;
if ($table->hasColumn('id')) {
$this->execute("CREATE SEQUENCE IF NOT EXISTS {$tableName}_id_seq START {$next};");
$this->execute("ALTER TABLE {$tableName} ALTER COLUMN id SET DEFAULT nextval('{$tableName}_id_seq');");
}
};
}
}
Get a list of Timezones out of Postgres
SELECT
name,
abbrev,
utc_offset,
is_dst
FROM pg_timezone_names;
Get a list of Timezones out of MySQL
USE mysql;
SELECT * FROM `time_zone_name`;
0 Comments