wtf? Incorrect datetime value

[dfelton]

Occurred while the ./bin/gemini logger:trade-history command was chugging along...

Shutdown Enabled at: 2020-08-27 22:01:36
Exception: Zend\Db\Adapter\Exception\InvalidQueryException
Code: 0
Message: Statement could not be executed (22007 - 1292 - Incorrect datetime value: '2020-03-08 02:42:40' for column `trade_history_btcusd`.`trade_date` at row 1)
Strace:
#0 vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(307): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
#1 vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(263): Zend\Db\TableGateway\AbstractTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#2 src/Command/Command/Logger/TradeHistory.php(173): Zend\Db\TableGateway\AbstractTableGateway->insert(Array)
#3 src/Command/Command/Logger/TradeHistory.php(108): Kobens\Gemini\Command\Command\Logger\TradeHistory->logTrade(Object(stdClass))
#4 vendor/symfony/console/Command/Command.php(255): Kobens\Gemini\Command\Command\Logger\TradeHistory->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#5 vendor/symfony/console/Application.php(1000): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#6 vendor/symfony/console/Application.php(271): Symfony\Component\Console\Application->doRunCommand(Object(Kobens\Gemini\Command\Command\Logger\TradeHistory), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#7 vendor/symfony/console/Application.php(147): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#8 bin/gemini(339): Symfony\Component\Console\Application->run()
#9 {main}

Previous Exception:
Exception: PDOException
Code: 22007
Message: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2020-03-08 02:42:40' for column `trade_history_btcusd`.`trade_date` at row 1
Strace:
#0 vendor/zendframework/zend-db/src/Adapter/Driver/Pdo/Statement.php(239): PDOStatement->execute()
#1 vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(307): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
#2 vendor/zendframework/zend-db/src/TableGateway/AbstractTableGateway.php(263): Zend\Db\TableGateway\AbstractTableGateway->executeInsert(Object(Zend\Db\Sql\Insert))
#3 src/Command/Command/Logger/TradeHistory.php(173): Zend\Db\TableGateway\AbstractTableGateway->insert(Array)
#4 src/Command/Command/Logger/TradeHistory.php(108): Kobens\Gemini\Command\Command\Logger\TradeHistory->logTrade(Object(stdClass))
#5 vendor/symfony/console/Command/Command.php(255): Kobens\Gemini\Command\Command\Logger\TradeHistory->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#6 vendor/symfony/console/Application.php(1000): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#7 vendor/symfony/console/Application.php(271): Symfony\Component\Console\Application->doRunCommand(Object(Kobens\Gemini\Command\Command\Logger\TradeHistory), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#8 vendor/symfony/console/Application.php(147): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#9 bin/gemini(339): Symfony\Component\Console\Application->run()
#10 {main}

[dfelton]

Value attempted to be inserted outcome of this: gmdate("Y-m-d H:i:s", (int) \substr((string) 1583635360570, 0, 10))

Not sure what's up yet. Prior record and all others for that matter worked fine. Prior value: gmdate("Y-m-d H:i:s", (int) \substr((string) 1583627499792, 0, 10)) (yielding 2020-03-08 00:31:39.000 in the db)

column description:

+-----------------+---------------------+------+-----+----------------------+--------------------------------+
| Field           | Type                | Null | Key | Default              | Extra                          |
+-----------------+---------------------+------+-----+----------------------+--------------------------------+
| trade_date      | timestamp(3)        | NO   |     | current_timestamp(3) | on update current_timestamp(3) |
+-----------------+---------------------+------+-----+----------------------+--------------------------------+

which by the way, what the hell is there an on update current_timestamp(3) here? granted these records, once inserted, are never intended to be updated, ever. Still, this value shouldn't change on update.

[dfelton]

Fucking timezones...

MariaDB validates DATETIME literals against the session's time zone. For example, if a specific time range never occurred in a specific time zone due to daylight savings time, then DATETIME values within that range would be invalid for that time zone.

Sure enough, March 8th was daylight savings time. "Spring Forward", and 12:31AM never happened.

Fuck.

[dfelton]

Update:

When the database server is set to +00:00 this is a non-issue, and probably should be what we should have the database set to anyways. Locally I've updated the time_zone setting on the global settings of the machine, but a better solution here will be to revise the database adapter to always connect to the server with +00:00 regardless of what the server is set to (or, after connecting check the value and throw an exception if it is not what we expect).

[dfelton]

Twice a year issue, and updating database server fixes it immediately. Will still want to revise application to force this configuration for every connection session.