Inconsistencies in default dates to 0000-00-00 and 1000-01-01

[pakricard]

Hi all:

webERP has mixed code when date fields default to 0000-00-00 and 1000-01-01.

1. Commit from 2018-12-01 stated, "Alter all date and datetime fields to have valid defaults '1000-01-01' as '0000-00-00' no longer acceptable"
2. install/.sql and install/tables/.sql use 1000-01-01 as default date (191 matches)

but:

1. latest code has 50 date compares against 0000-00-00 and none against 1000-01-01 (checked *.php)
2. install/*.sql use 0000-00-00 as default dates or demo data (155 matches)
3. Recent sql/updates/*.sql use 0000-00-00 as default dates

So, in the latest code:

1. some date fields default to 0000-00-00 and some to 1000-01-01
2. Current code use date compares against 0000-00-00 to dates defaulting to 1000-01-01, potentially leading to wrong results.

Then, we should all agree in using one format or another as default, for consistency and ease of maintenance of the code. I would go back to 0000-00-00 in all cases, as there will be minimal modifications in code and according to Gemini, there are no significant differences in favour of one or another.

• Anyone else agrees with returning to 0000-00-00?
• Anyone think we should go to 1000-01-01?

Once agreed, I can take care of the code revision.

Thanks,
R

According to Gemini:
In MariaDB, neither 0000-00-00 nor 1000-01-01 is inherently the "best" default date value. The most suitable choice depends on your specific use case and data requirements.

0000-00-00 (Zero Date):

• Special Value: It's a recognized special value in MariaDB, representing a "zero" or "null" date.
• Data Space: Uses less storage space than a valid date.
• SQL Mode: Its usage is influenced by the SQL_MODE setting. With NO_ZERO_DATE enabled, it's not allowed and will result in an error.
• Interpretation: Can be interpreted as a placeholder for missing or unknown dates.

1000-01-01:

• Valid Date: A valid date within the supported range of MariaDB's DATE type.
• Meaningful Value: Can be used as a default value with a specific meaning, such as a "date of creation" or "start date."
• Data Space: Requires more storage space than the zero date.

Choosing the Best Default:

• Missing or Unknown Dates: If you frequently encounter missing or unknown dates, 0000-00-00 can be a suitable choice, provided you handle it appropriately in your application logic.
• Meaningful Default: If you want to assign a specific default value, such as a "date of creation," 1000-01-01 or another relevant date can be used.
• SQL Mode: Consider the SQL_MODE setting in your MariaDB environment, as it can affect the usage of 0000-00-00.

Example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    created_at DATE DEFAULT '1000-01-01'
);

In this example, 1000-01-01 is used as the default value for the created_at column, assuming it has a specific meaning in your application.

Ultimately, the best default date value depends on your specific needs and how you intend to use the data.

[dalers]

From a position of almost complete ignorance, my vote for a default date is 0000-00-00 because 1000-01-01 is a valid date, which would be incorrect if the intent is "no date".

However, if the amount of work to "undo" special handling of 1000-01-01 is significant, or inconsistencies between MariaDb and MySQL in this regard make 0000-00-00 problematic, I can accept 1000-01-01 for "no date" as simply being consistent is more important.

Almost as important as the convention is how to document it. I suggest a developers' page in the wiki listing all conventions - keyword capitalization, spaces vs tabs, default date, etc.

Cheers and best wishes to everyone for a healthy and prosperous New Year!

[aingelc12ell]

Good catch @pakricard and great insights @dalers .

As MySQL shifted from the zero date conventions some years back, this issue had been one of the headaches to deal with -- UPGRADE and MAINTAIN strategies we all do.

I suggest the following, moving forward:

1. We provide the option to others to either use SQL_MODE with ALLOW_INVALID_DATES, especially those that can modify them.
2. We provide function(s) that will transform date values for TRUE and FALSE conditions based on dates: no date, valid, etc.

[pakricard]

Hi @aingelc12ell : Thanks for the detailed info.

As MySQL shifted from the zero date conventions some years back

Then, probably move with the tide and apply 1000-01-01 as our existing 0000-00-00.

The work to be done is not much either way: As I see it:

• For every table with dates as 0000-00-00: UPDATE table SET date = "1000-01-01" WHERE date = "0000-00-00";
• For every date field defaulting to 0000-00-00: ALTER table to set the new default to 1000-01-01;
• Search and replace all 0000-00-00 into 1000-01-01 in the code

I think that's all.

Let's wait a few more days if someone else has a different POV