gnucash and mysql: Incorrect datetime value for column 'post_date'

[John Ralls]

> On Oct 16, 2017, at 2:50 AM, joerg_diederich at freenet.de wrote:
> 
> Hello,
> 
> I'm trying to convert my existing gnucash account from xml to mysql. I created a new mysql database, but when I try to save my data into this database with gnucash I get the following error message:
> 
> <--- cut here --->
> mysql error: Incorrect datetime value: '18981201105900' for column 'post_date' at row 1
> <--- cut here --->
> 
> In another thread I found the recommendation to adjust the sql_mode using
> 
> sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
> 
> Unfortunately, this doesn't change the behaviour for me.
> 
> 
> Here are some version information:
> 
> Operating System: Linux opensuse LEAP 42.2
> gnucash:               gnucash-2.6.16-3.3.1.x86_64
> mysql:                  mariadb-10.0.31-20.7.1.x86_64
> 
> 
> Any hint how to get rid off this error is really appreciated.

We need to change the schema for MySQL/MariaDB to use DATETIME instead of TIMESTAMP; the latter’s date range is only 1 Jan 1970-15 Jun 2038. It’s on my to-do list for 2.8.

For 2.6 you’ll need to use Postgresql instead if you need your books to run before 1970.

Regards,
John Ralls