Page 1 of 1
Default date 01/01/1970 when a calculated field is null
Posted: 2021-04-06 15:23
by balfons
Hi everybody!
I have several date calculated fields and when there is no date to show, AppGini prints 01/01/1970. I have made a workaround in one field with a SQL query that puts a forecasted date 3 months later, but it only wprks for this field.
So, I am finding a solution for this fields. Any suggestion?
Thanks in advance!
Re: Default date 01/01/1970 when a calculated field is null
Posted: 2021-04-06 16:31
by jsetzer
Is it date, datetine or timestamp?
Re: Default date 01/01/1970 when a calculated field is null
Posted: 2021-04-06 16:39
by jsetzer
Additionally: can you check if all database migrations have run (update fields in admin area)
and
is the field nullable, does it have a default value
and
can you post the SQL command of your calculation query
Re: Default date 01/01/1970 when a calculated field is null
Posted: 2021-04-07 11:34
by balfons
Hi Jan!
it is a date field, read only (because is a calculated field) and doesn't have a default value. I have also checked that all database migrations have run.
Here is my code:
SELECT date_format(`table2`.`execution_date`,'%d/%m/%Y') FROM `table2`
LEFT JOIN `table1` ON `table2`.`corrective_action`=`table1`.`id`
WHERE `table1`.`id`='%ID%' AND `table2`.`status` = '2'
ORDER BY `table2`.`execution_date` DESC LIMIT 1
table1 is parent of table2. I want to show in table1 the date of the last record of table2 having a certain status. It works when I have some records that fulfil the condition but when I have no records, 01/01/1970 is shown.
thanks for helping!
Re: Default date 01/01/1970 when a calculated field is null
Posted: 2021-04-07 12:08
by jsetzer
That's strange because MySQL's number_format function should return NULL on null or empty input but not "1970/01/01" (AKA: UNIX Epoch).
I've checked it in Adminer:
Code: Select all
select date_format('2021-04-07', '%d/%m/%Y');
select date_format('', '%d/%m/%Y');
select date_format(null, '%d/%m/%Y')
Results
Code: Select all
select date_format('2021-04-07', '%d/%m/%Y')
07/04/2021
select date_format('', '%d/%m/%Y')
NULL
select date_format(null, '%d/%m/%Y')
NULL
I've checked with 0:
Code: Select all
select date_format(0, '%d/%m/%Y');
// result in date-column: 0000-00-00
I've checked with unconvertibles:
Code: Select all
select date_format('aaa', '%d/%m/%Y');
// result in date-column: null
None of my tests returned UNIX Epoch from MySQL Query. This means for me that even if there is no value due to inner join date_format should return something different than UNIX Epoch.
So, the problem may be NOT on MySQL side but on PHP side. I know that, when calling PHP's date() function with an empty or invalid input parameter, it returns UNIX Epoch unless you convert the input using strtotime before.
I am wondering if this is just a display problem or a database problem. Can you please check your database table if whether 1970/01/01 has been stored or null or 0000-00-00.
Re: Default date 01/01/1970 when a calculated field is null
Posted: 2021-04-07 13:50
by balfons
Hi Jan!
I have checked database and the value stored is null so maybe is a display problem