1084

Passing null date values to database

Comments for “Passing null date values to database”
 

Posted by KeepCalm on Saturday 23rd July 2022 at 20:13 GMT

Is it possible to have null date values in create and update modules in Trongate?
My database does accept null values on the date columns(using Mariadb 10+), however it seems that Trongate sends an empty string if no date is selected. This results in the default Unix date of 01/01/1970 being inserted in the date columns and displayed on the forms.
Any guidance will be appreciated.

Cheers!

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

Posted by KeepCalm on Sunday 24th July 2022 at 00:44 GMT

To be more specific, I have a form where there are fields for $date_assigned, $date_completed and $date_sent, which would not be filled until a task is assigned to a an employee and processed.
This means that these fields should remain null until filled out, but upon saving a new record these fields display 1970,01/01 instead of the expected null or blank values.
I am not sure whether is due to the validation rules on these fields(datepicker_us) or Trongate sending empty string values upon update.

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

Posted by KeepCalm on Sunday 24th July 2022 at 02:37 GMT

This issue is resolved.
It turns out that mariadb assigns a special date value of 0000-00-00 to null date fields, but Trongate will try to format an empty date field, which results in mariadb assigning an value of 1970-01-01.
So the fix is to force Trongate to pass a null value to the date fields on create/update then to format the date fields as needed.in show/manage views.

Cheers!

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

Posted by DaFa on Sunday 24th July 2022 at 09:11 GMT

Hi KeepCalm,

I assume you are using the desktop app to generate your module. If so, it is the donor code in the submit() function not Trongate itself causing the issue. You should find a line that may look like this (any_date) is just for example:
$data['any_date'] = date('Y-m-d', strtotime($data['any_date']));

which if 'any_date' was an empty string "", the above would convert and save it to the db as '1970-01-01', which is a signed 32bit Unix timestamp for '00:00:00'. The Unix timestamp is rather flawed in design and let's hope they fix the bug before 03:14:07 Jan 19 2038 :)

Bit of reading for you:
https://en.wikipedia.org/wiki/Unix_time
https://en.wikipedia.org/wiki/Year_2038_problem

If you were to alter the above code to:
$data['any_date'] = ($data['any_date'] == "" ? NULL : strtotime($data['any_date']));

If 'any_date' where = "" the above turnery would save it as NULL in the db or a valid date if not.

You would still need to alter your 'manage' and 'show' or any views to deal with NULL dates

Cheers,
Simon

This comment was edited by DaFa on Sunday 24th July 2022 at 09:17 GMT

Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

Posted by KeepCalm on Sunday 24th July 2022 at 11:16 GMT

Thank you Simon,
Your recommendation with the ternary is exactly what I implemented to resolve this issue.
Thanks for the extra info about the issues with the Unix time.

Cheers!

KeepCalm

User Level: Guest

Date Joined: 15/07/2022

×