Discussion: Default values for database fields of type "datetime"

[muratpurc]

With the issue #22, we have fixed posible errors with dates in database fields having the "NOT NULL" constraint and "0000-00-00 00:00:00" as a default value. Almost all occurrences "0000-00-00 00:00:00" has been replaced with CURRENT_TIMESTAMP.

But I see a problem in some database fields by using CURRENT_TIMESTAMP, for example:

• con_user.last_pw_request: The value of it will be used for time limit comparsion, see setting (type = 'pw_request', name = 'outdated_threshold'). A new registered user has to wait a certain amount of time before requesting a new password.
• con_art_lang.published: Usually, the published date will be set, if an article goes online. Setting it CURRENT_TIMESTAMP to is wrong and could be misleading.
• Other fields might be also affected by the CURRENT_TIMESTAMP

We should think about removing the "NOT NULL" constraint for some fields for those where we need this constaint, we could use a datetime in the past, e. g. "1970-01-01 00:00:00".

Any other ideas or suggestions are welcome?

[muratpurc]

Table con_frontendusers has the fields valid_from and valid_to, both without the NOT NULL constraint. We should use this also for table con_user. It would be consistent, we don't have to think about what date to use instead of '0000-00-00 00:00:00' or CURRENT_TIMESTAMP, and I see also no backwards compatibility issues.

[muratpurc]

Please follow the discussion in CONTENIDO forum:
https://forum.contenido.org/viewtopic.php?f=115&t=43800

[muratpurc]

The result of the disussion im forum is as follows:

We don't need the "NOT NULL" constraint for the database fields, therefore we can remove it from almost all database fields. Database fields of type datetime should have a NULL value and this will make the usage of CURRENT_TIMESTAMP obsolete. This solution should work for almost all cases. I'll create an issue for it.